Questions tagged [database-design]
For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
1,244
questions
-2
votes
1answer
49 views
Should cached data be kept separate from primary tables?
I have a table in an SQL database for storing information about some business object that looks something like this (details changed so as not to give away what company I work for):
CREATE TABLE ...
0
votes
0answers
92 views
Design MySQL database to store user workout data for Workout App
I am building a fitness app and I'm trying to figure out the optimal way to store the data is a MySQL database.
I would like to have specific programs (for example, 'get jacked'), I would like a users ...
1
vote
0answers
30 views
How to handle storing of arbitrary data in an application enablement platform
I'm working on an AEP in the IoT space, where we've created a few domain-specific entities to allow users to model their data. Each entity has both a type and instances of that entity. These are:
...
0
votes
2answers
90 views
How do I structure the db effectively to query historic data like "Who was the HOD of the department at a given point in time"?
First of all I am not sure if this question should be here or in stackoverflow. I decided to post it here because it is not a coding but architectural challenge.
Introduction
I have a data base design ...
0
votes
0answers
26 views
Django Model Design
I am in the design phase of a project and I am having some trouble finding a solution that can perform queries efficiently with PostgreSQL backend
Say, there exists an item and each item has fields ...
-3
votes
1answer
113 views
Most efficient way to get average data for thousands of records from the database [closed]
I have a database that keeps a temperature readouts, which are being added - let's say - every minute:
+----+---------------------+-------------+-----------+
| id | timestamp | temperature | ...
0
votes
0answers
24 views
How to structure my Google Firestore DB for a crypto signal application
I'm brainstorming how to structure my Firestore DB and would love others input as I'm not a backend developer and generally new to software dev.
Backend Stack
Firebase
Firestore
Cloud Functions
...
0
votes
1answer
90 views
Time consideration in multiplicity of classes
In the following image, regarding a CD shop that rents its music CDs.
My question is regarding the correct multiplicity of X. I can think of several possibilities for X:
X= 1..1 as each copy of ...
0
votes
1answer
86 views
SQL Foreign Key Relationships and External Data
I work for a company that wants an external system built outside of their core system. I can't share an SQL database, and can't store data in their database. So I am forced to maintain my own SQL ...
-3
votes
1answer
34 views
Creating a Django web-app with Sage 200 Database
I have been asked to create a Django/Python web app that creates web-based, .pdf and excel reports from a Sage Evolution database. While the sage front-end is still being used. My client essentially ...
-1
votes
2answers
153 views
Is it normal and good practice to enlarge the amount of columns when adding new features
There is a table named is A with 40+ columns, among which around 10 are columns of hierarchical levels, in a data analysis platform maintenance and development ( legacy ) project. Due to development ...
1
vote
1answer
72 views
Syncing and matching local records to external database that behaves like a black hole
Black hole problem
I am working on matching and daily syncing of our internal data records to an external database that behaves like a black hole, meaning, we cannot view nor extract any data from it ...
1
vote
2answers
26 views
Data Model for abstracted transactions table that combines more granular transactions
A fast food restaurant has an existing sql table of transactions:
old_transactions:
transaction_id
item_description
quantity
price
1
burger patty
1
5
2
bun
1
1
3
ketchup
1
0.25
4
packaging
1
0.50
...
-1
votes
1answer
77 views
Should I use a database as gateway to other services? [closed]
The context
I have an IoT project where the sensors are sending data to my Postgres database. The sensors are manages by a stand-alone service which provides a REST API to query various information ...
1
vote
3answers
114 views
What makes a data pipeline scalable? Best practices for scalable design?
I have been searching about this topic for a few days and have not yet found anything on books, courses or tutorials.
What is a way to make data pipelines more scalable, that doesn't involve NoSql or ...
0
votes
1answer
133 views
Developing a database: How to keep track of log files
I am building a database, and have implemented a transaction log system to ensure that writes are atomic, idempotent & consistent.
When a transaction is 'committed' it is appended to a log file ...
0
votes
3answers
76 views
Fetch the most recently updated records in a database across multiple tables in a performant manner
I have an Angular + Django web application in which Django functions as my backend.
In this web application, I have 12 different tables about articles, each table representing a different kind of ...
0
votes
2answers
45 views
Where to sync Database and Search-Database in MVC pattern
I have a website-sideproject (Application Server: Django, Database: sqlite) with "search" functionality and I've recently stumbled over this problem.
When you have a full-text-search-...
0
votes
0answers
19 views
How should I structure my collection and documents to create a chat app, using Googe Cloud Firestore and react native?
I am writing a chat app. Each user has a profile, so every user can message to another user (one to one).
My idea so far: Once a user message to another user I call createChat function:
const ...
0
votes
1answer
205 views
Recording history - All changes in one table, or three tables?
We're starting to record history in an about 5 y/o internet system built with ASP.NET Core and SQL Server.
Our current requirement is to save all records from a specific table, plus two fields from ...
-2
votes
1answer
116 views
Software architecture for a dynamic reporting system
I have to create a reporting system based on hundreds of different tables in a database (which means a huge volume) and I'd like to know the best practices and/or your wild ideas about it.
Here are ...
1
vote
1answer
65 views
Dealing with queries on versioned data
I have a dataset stored in ElasticSearch, only for searching purposes (the source of truth is external).
Each index have an associated schema and there are associated queries.
From time to time, the ...
0
votes
1answer
100 views
Relationship problem between entities
I'm not sure how I should think regarding my database design. I have made this database schema below. And I'm trying to think about the relationship between the entities. I have read through online ...
-2
votes
2answers
112 views
How can we implement an incremental naming system?
Just like how Mac or Windows gives incremental names to new folders, how can we implement such a system in a database?
Let's say I have currently 3 folders (default names) i.e., "untitled folder&...
2
votes
1answer
80 views
What is a good approach to handle editing product images?
You have the following situation:
You have an SQL database containing two tables product and product_images
You would like the user to be able to add, remove, or move around existing images
They can ...
0
votes
0answers
22 views
Database architecture for Email Marketing which can be used as a micro service
I am working on a very challenging email marketing database design and i am not able to figure out the best way to build the database, where functionality can be split so it can be used as a ...
0
votes
1answer
96 views
Architecture for alerts system based on API - would this be too slow a design?
(Hi I’m new here - I believe this question suits here from the guide saying “ architecture, and design” - however if there is a better place, just pop me a comment. As there’s no actual code it doesn’...
4
votes
2answers
908 views
Thoughts on database denormalization via short-circuit key
In the simplified database schema depicted below, I'm considering denormalizing by adding a short-circuit key (EventId, relation shown in red) on Appointment.
One advantage is not needing a join to ...
0
votes
1answer
50 views
What are the benefits of having database in separate instance to main application?
Positive:
Allow for horizontal scaling of app by replication of instances - without replicating the DB
Drawback:
latency introduced between 2 machines
Is this something necessary for small systems?...
0
votes
0answers
26 views
Best way to see changes to non-monitored linked tables
I am hoping that I can get feedback on my solutions, and other ideas as well.
We have a table in a database being monitored by a NodeJS app, using the MYSQL binary logs, E.G.:
ORDERS:
orderID, ...
1
vote
1answer
78 views
Showing posts by their time-decayed score
I want to show a number of posts, ordered by their score, limited to some number. The score is the product of the number of likes they have (from another table) and the recipricol of their normalized (...
0
votes
1answer
94 views
Best practice or patterns to couple complex custom objects to custom logic
In many project that grow more complex at a certain moment business rule editors, workflow editors, custom taxonomies and such enter the arena.
In all of these a coupling is made between a complex ...
3
votes
1answer
91 views
Is it 3NF to use a compound key, where parts of the key are also FKs
For example:
PostCode
PK PostCode
Address
PK HouseNumber
PK PostCode FK
Seems reasonable. But can we carry on indefinitely?
Room
PK HouseNumber FK1
PK PostCode FK1
PK ...
-1
votes
2answers
90 views
Contemporary Design and One-to-One Relationships: code-first vs data-base first approaches
After reading, Does it ever make sense NOT to condense one to one relationships?, I wanted to take the question further with regards to contemporary design of entities, front-end code and controllers. ...
0
votes
2answers
53 views
Structure database for timeline with different objects
I need to work on a project where we have a "case". Within this case we need to attach different things, in a timeline.
Those things can be:
Status change (Open, On hold, Closed)
Messages (...
0
votes
2answers
72 views
How should design my entities when I sometimes need only a subset of fields?
I have a class that is mapped using Entity Framework. This class has dozens of fields, and in the database there are millions of rows for this class. The class looks like this
public class MyEntity
{...
0
votes
1answer
58 views
Is there a difference between these two data models?
Let us say I want to store on a database a userID, their nationality and if they are from the UK their UK passport number.
I could model this by having one table with userID as primary key, ...
50
votes
6answers
10k views
Is denormalising a database for speed an anti-pattern?
I have a database with a 1:m relationship.
I have to display a list of parents to the user rapidly on a home screen at startup. The parent shows a single piece of information that is a sum of a ...
-2
votes
2answers
85 views
Database Structure for The Best Efficiency
I want a database table that can hold all of the group chat messages from all of the group chats.
The max amount of users per group chat would be 20.
The max amount of group chats per user would 25-...
-1
votes
1answer
88 views
Design pattern to sync local databases with the cloud [closed]
I would like to find the best way to solve the following problem:
I need a database for a software. The software needs to use the data to populate the UI and allow the user to interact with them (edit,...
0
votes
0answers
15 views
Abstracting resource permissions in noSQL
I'm building a booking system and my team is currently constructing a web application for the system where a staff member should be able to manage all the bookings related to a user with permission to ...
0
votes
0answers
24 views
Storing common data across tenants in NoSQL databases like mongoDB
We are designing a multi-tenant architecture. In our multi-tenant architecture, there are different email templates that are shared across all the tenants.
Email template looks like
subject: {...
0
votes
2answers
80 views
Extend the ApplicationUser class or use a Person class?
I am making a web system using Asp.Net Core with individual user accounts (Identity), and I was thinking about how I should store the users' personal information in the database.
The system will have ...
1
vote
3answers
135 views
How do I make sure a task run only once in a dockerized environment?
I have a task that I need to make sure it only runs once in my dockerized environment (managed by k8s), running the post-upgrade script is one example.
Because each dockerized app can run the task and ...
1
vote
1answer
81 views
Feature Store Design for input data like JSON, CSV and Excel
I need a design approach for a feature store which accepts input data in the forms of CSV, EXCEL, JSON etc.
The backend stack we support is Java, Spring boot and oracle DB.
Note :
Each file ...
0
votes
1answer
31 views
Database schema for two different types of elements on map
I wrote a software for a customer that shows clients on a map (Google Maps).
I store the clients on a table on the MySQL database (table clients) and show them on the map using markers. Each clients ...
1
vote
0answers
37 views
Looking for the right design (pattern?) for my webapp and external APIs intgerations
I'm working on a simple CRM (Customer Relationship Management) webapp (Flask and Peewee ORM) and I found myself in a situation where I believe that I should have used a design pattern that I may not ...
2
votes
0answers
29 views
Populating form options in dynamic web form
I am looking for advice on database design for a web app I am building.
In the web app (Angular, REST API, Oracle SQL database), I am constructing a web form dynamically. I chose dynamic form ...
0
votes
1answer
64 views
Displaying various subsets of content stored in a database
I am looking for advice on database design for a web app I am building.
Part of the website needs to display content from a SQL database dynamically depending on the component's context. To be more ...
3
votes
3answers
282 views
What benefit does a junction table provide over adding an additional field with a foreign key?
Consider this simple example:
We have the apartments table below:
ID
Address
0
555 Maple St
1
123 Oak St
2
999 Pine St
We also have the tenants table below:
ID
Name
0
Bob Foo
1
Jane Bar
If I ...
