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,346 questions
1
vote
1
answer
123
views
Where should authentication data be stored in a modular monolith with multiple authentication strategies? (NestJS)
I’m implementing the authentication module in a modular monolith built with NestJS.
Initial Draft
In the first draft, the User entity included the hashed password, refresh token, and refresh token ...
- 69
0
votes
1
answer
158
views
Optimal way to avoid iterating through each row in a dictionary of data/arrays?
I have an excel macro that imports daily share price files, and finds the highest price for a share after a given date.
Each of these daily stock price files has ~1000 rows of data. Currently I have ...
0
votes
2
answers
154
views
Designing a social media notification system, would a single table with JSON column be the correct approach for data?
I am trying to create a notification system similar to Instagram, for the lack of better comparasion.
The Goal:
Getting user notifications in a unify way, for easy unparsing and displaying to user.
...
- 101
1
vote
2
answers
353
views
Should you ever have one-to-one relationships between entities?
One-to-one relationships occasionally evolve into one-to-many relationships. Once that happens, your team has a tricky job of refactoring the datastore, the backend to allow that change.
For example, ...
- 2,030
3
votes
2
answers
154
views
How to determine which service keeps the relation to the other?
Suppose I have (micro)services:
The following is simplified a lot, you may assume they're significant enough to be a micro.
Reseller Service - Our code is turned into whitelabel software. This is the ...
- 757
5
votes
3
answers
433
views
When is multiple validation layers of protection necessary?
I'm having a hard time of understanding at what point is multiple layers of validation protection necessary rather than a single point of failure and if the performance hit is a concern
Lets say you ...
0
votes
1
answer
164
views
Reorganize data with pattern while migrating from SQL to MongoDB
I have several tables in Oracle, in each of them are stored data about a different kind of tax declaration (house tax, hotel tax, and so on). All of them have some data in common, so I want to migrate ...
- 531
1
vote
1
answer
241
views
System design for tracking viewed posts and returning unseen posts
I came across this system design question and have been wondering what is a good approach.
Requirement :
We have a typical blog or a mini social media kind of website where users create "Posts&...
- 154
0
votes
1
answer
166
views
How to Design a Database for Both Flexibility and Data Isolation?
I am designing a system that serves multiple customers, providing data visualization for revenue and membership information.
Currently I use a separate schema approach to isolate customer data. Each ...
- 43
3
votes
3
answers
312
views
Aligning domain model to data model. Is my data representation wrong?
Engineering Community,
I am currently writing my bachelor's thesis on microservices and DDD (this is not the actual topic, but it is part of it). The language I am writing in is Rust, but that is ...
- 39
21
votes
5
answers
4k
views
How manage inventory discrepancies due to measurement errors in warehouse management systems
I'm developing an enterprise-grade warehouse management application for a chemistry laboratory. A critical feature involves mixing multiple stock solutions to create new solutions based on predefined ...
- 321
2
votes
2
answers
464
views
Best design pattern to synchronize local and cloud databases?
Right now I have an edge device (resource constrained) which is processing, storing, and querying time series data using a Postgres DB.
When a data collection event has ended, a simple script on the ...
- 29
2
votes
3
answers
414
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g. name, email, phone_number, address, etc.)
Work-...
- 131
1
vote
3
answers
296
views
How to implement an DB index to allow intersection queries on a database which does not support compound keys? [closed]
I am creating a DB that indexes JSONs on top of a key value storage engine (LMDB or somewhat similar).
When a new JSON needs to be indexed, I will create an entry for each field (AKA, JSON key), for ...
- 233
6
votes
9
answers
950
views
If services own their data, how do you deal with relations?
I've been studying different flavors of Service Oriented Architecture (SOA), Event-Driven Architecture (EDA), micro-services and related topics for the past few days.
I see it often recommended that ...
- 179
2
votes
1
answer
1k
views
One and only one vs One in crow's foot notation
Am I using one and only one correctly? I have read so many articles online on differences between one and only one vs one and I'm still confused.
I.e.
a customer can exist with zero, 1 or many (...
- 29
17
votes
8
answers
4k
views
How do we distinguish between "not filled in" and "unknown" in our data store?
In our domain model "not filled in" and "unknown" are two different concepts. For example, time of death may be missing or, on the other hand, may be filled in as "unknown&...
- 2,030
0
votes
1
answer
130
views
How to model a bidirectional, one-to-one relationship in DynamoDB?
I'm building a WebSocket API Gateway in AWS. When a device connects, API Gateway assigns the connection a unique connection ID. I need to store the relationship between the connection ID and device ...
- 1
0
votes
1
answer
119
views
Database structure for two-step registration flow
I'm trying to design a database for supporting a multi-step registration flow. The registration flow goes like this: the user logs in via OAuth (which creates a session and user), then they're asked ...
- 123
1
vote
2
answers
168
views
Why do developers put asymmetric items into a single database table? [closed]
A pattern I've seen several times is:
Customer table - CUSTOMER ID,
Contact table - CUSTOMER_ID, CONTACT_TYPE, CONTACT_NUMBER, OTHER
The use case for the tables is:
SELECT
Home_Num.CONTACT_NUMBER ...
- 323
1
vote
1
answer
140
views
Fixed vs dynamic properties for a system with customization and changing requirements
Edit: writing a more specific question, as per comment, I guess my question boils down to: are dynamic properties designs appropriate for applications with rich UI and complex business logic (as ...
- 113
16
votes
6
answers
5k
views
Is it OK to use the same field in the database to store both a percentage rate and a fixed money fee?
I am trying to convince a colleague that their proposed approach to storing a "fee" is problematic.
The "fee" can be either
a percentage rate, e.g. 50%, which is stored in the ...
- 278
0
votes
1
answer
101
views
if an entity depends on two other entities(that have a relationship between them) should I make the enitity depend on the relationship instead?
If I have three entities (end-user, country, tier) in my system that has relationships as follows:
Where each country has its own tiers with specified points per year, and each end-user has a country ...
- 109
0
votes
0
answers
83
views
How to restructure a schema in order to support conditional (one of two tables) relations
I've been stuck on this for a while now so I decided to ask for some help.
My SetTemplate needs to EITHER be connected directly to exerciseTemplate OR connected to exerciseTemplate through a ...
- 11
1
vote
2
answers
356
views
How to design user-defined ordering of items?
I've got a site (here) that lets users publish labs. I'm looking at adding a feature where users can customise the order that their labs appear in. To be clear, I mean that they can manually control ...
- 1,675
1
vote
2
answers
129
views
Linking records through multiple tables?
I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of ...
- 159
2
votes
1
answer
308
views
What is an optimal system design for tracking product views per user that is scalable?
I have a web application that contains products and users. There are 10,000+ products and 100,000+ users to give a sense of the scale that's required.
For some application specific reasons, I need to ...
- 29
2
votes
1
answer
429
views
Does it make sense to use a graph database (Neo4j) for a diagramming app?
I am developing a diagramming application and want to optimize operations with the Nodes and Relations of the diagram. Currently, I am using a relational database with tables for diagrams and nodes. ...
- 29
2
votes
1
answer
915
views
Designing a flexible and comprehensive restaurant menu database schema?
I am working on developing a MySQL database schema for a restaurant menu system inspired by platforms like DoorDash, UberEats, and Yelp. My aim is to design a schema that is flexible and can handle ...
- 145
0
votes
2
answers
349
views
Advice for database design and approach
I ask this to learn from experienced DB designers/architects. I need a high-level overview, I'm not interested in the specific tools for now. Rather, I'd like opinions on how you would approach this ...
- 41
-1
votes
1
answer
76
views
Ensuring unique indexes on nosql datastores [closed]
I am building a User Service. User would be able to register with emailId or phone no. Upon successful registration, they can choose a username (which should be unique)
It is easy to do on SQL ...
- 144
-1
votes
2
answers
266
views
Best Practices for Storing Operational Hours Extending Past Midnight in MySQL
I'm building a PHP application to manage business operating hours, including cases where the closing time extends past midnight. I'm considering various ways to store these hours in MySQL and need ...
- 145
-1
votes
1
answer
749
views
Would it be a good design to have multiple repositories for one entity?
I am working on a project. I have designed the codebase all the way from the scratch. Basic thing is the project is divided in individual modules. The modules represent one complete part of a business ...
0
votes
1
answer
79
views
Database Modeling for Application with Many Integrations
I’m working on an application that requires many 3rd-party integrations. I’m using Postgres to store these API responses but am having trouble for a number of reasons.
These objects are frequently ...
- 109
0
votes
1
answer
61
views
Where to store config when using a external binary in a web service?
I'm making a vpn service that can be controlled through admin web page.
So I have a web page that consists of front-end page and a backend API + DB.
In this admin web page, I can execute a vpn binary ...
- 121
0
votes
0
answers
289
views
Approach for comprehensive data/activity logging
I would like to be able to build up a log of user activities, capturing data such as who they were, where they logged in from, what activity did they take, and what data did they change (both before ...
- 275
3
votes
1
answer
249
views
Handling Back-dated Transactions and "Online" Transactions on ERP/Banking Systems
I was currently developing a PoC of a "core banking system" in my current company, a pawn shop that generally offers lending services to retail customer and deposit services. We've built a ...
- 39
0
votes
1
answer
1k
views
Clarification on using timestamp without time zone and LocalDateTime in UTC-centric systems
I'm seeking clarification regarding the usage of timestamp without time zone in a database and its corresponding Java type, LocalDateTime. The current setup I'm working on involves servers and (...
user175557
0
votes
1
answer
134
views
Better way to track and analyze downloads (or any other events)
I need to decide about my database architecture. The app provides files for download. I can add a counter to every file and track total events. But the client may want some analysis and ask for the ...
- 153
-2
votes
1
answer
115
views
What is a route I could take when considering setting up a data warehouse for a small business? [closed]
I am working for a medium-sized development and construction company and we are looking to upgrade our data and BI. Currently, all project data is managed via Excel (data, project management, and ...
1
vote
1
answer
176
views
Monolithic Database to Microservices, but with Radical Data Model Changes
We are finishing up a long (over 1 year) project to retire our monolith. We have been moving pieces of functionality into microservices guided by domain driven design.
So far we have been leaving the ...
- 324
-1
votes
2
answers
692
views
Should I store large amounts of data in the properties of a node in a graph database?
I'm building a reddit clone on top of software that must incorporate a legacy neo4j graph database to track user actions. I also want to use it to store the comment/post data. Assuming this new app is ...
1
vote
1
answer
805
views
Designing a graph database structure for an application with a large amount of connections
I'm looking for advice on how to design the data model for a project I'm working on. I'm not really sure where to ask such a question. I've been using StackOverflow for years, but this problem is a ...
- 111
-2
votes
2
answers
271
views
Designing a database schema for a website related to my town's history
I am working on digitizing old photographs, articles, ephemera, etc. related to my town's history, and I've been tasked to put these scans I've made onto a website for the public to see. There are ...
- 13
12
votes
5
answers
8k
views
Is it an anti-pattern to create ORM entities based on existing database schema?
I maintain a large legacy app working with SQL database. The app uses raw SQL queries to query the database. Together with app rewrite I plan to introduce ORM to work with the database.
The simplest ...
- 277
1
vote
2
answers
1k
views
How to effectively scale one microservice to multiple instances
Let’s say I have a booking microservice (single instance) with its own DB.
If I want to horizontally scale the service with multiple instances(exact same copy of the business logic code) running on ...
- 147
2
votes
3
answers
944
views
Dynamically transform EAV data into standard SQL table
I use MariaDB to store data using EAV model. I need EAV, because each attribute of each entity can have its own validity (validFrom, validTo).
For example, I have entity person and I track changes ...
- 277
2
votes
2
answers
286
views
How to design fault-tolerant distributed "all-or-nothing" system?
I have a system where Client(C) sends request to Server(S0). S0 then sends the response back to Client that "request received" and closes the connection. C can regularly poll S0 to check on ...
- 33
3
votes
2
answers
227
views
how to deal with schema/constraint changes in insert-only Relational DBs?
as per request, I'll reduce this whole set of questions to 1-2 core points (thanks for the feedback ;)).
I'm considering using insert-only tables for keeping the history of my data. while the ...
- 41
15
votes
4
answers
5k
views
Better solutions than joining table for Many to Many?
Lets say I have students and classes which are 2 entities. A student can take many classes and a class can have many students. This would be a many to many relationship.
To solve this with an RDBMS my ...
- 345