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,326
questions
0
votes
0
answers
67
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
72
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,665
1
vote
2
answers
101
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 ...
- 167
2
votes
1
answer
149
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
1
vote
1
answer
89
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. ...
- 19
2
votes
1
answer
182
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
193
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
50
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 ...
- 134
-1
votes
2
answers
106
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
153
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
0
answers
39
views
Need advice on approaches to saving data
The following code(JSFiddle here) is a grid-based code where I'm performing the following steps:
I’m moving blue boxes from left to right (on the grid) after clicking Move Text Content! button. Here ...
- 151
0
votes
1
answer
62
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
51
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
123
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
2
votes
1
answer
127
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 ...
- 29
0
votes
1
answer
372
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
84
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
85
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
132
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 ...
- 304
-1
votes
2
answers
250
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 ...
0
votes
1
answer
267
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 ...
- 101
-2
votes
2
answers
198
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
7k
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
0
votes
2
answers
629
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 ...
- 137
2
votes
3
answers
479
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
240
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
127
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 ...
- 39
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
0
votes
1
answer
221
views
Database design for different type of user comment
I'm designing a database where a user can add notes on diffrent entity of the application.
For example, the user can add a note on the project and/or on the project line. The note added can have ...
- 59
1
vote
4
answers
334
views
Is it a good practice to allow null FKs?
I have Projects table. Projects can have costs, so I have a Costs table. A cost can be because of a product or beacuse of a service. So I have 4 main tables:
Projects (IDProject, Description...)
...
- 543
0
votes
2
answers
215
views
When should we separate common parts of multiple services into one separated service
We have some services (consider 10, some of them already exist and some are yet to come). All of these services have a common part where they keep track of what's being changed, and these so-called &...
- 101
1
vote
1
answer
407
views
Representing Rust enums in databases
Although I love Rust's enums (and the idea of making illegal states unrepresentable), I'm struggling to represent (ironic, yes) them in databases like PostgreSQL when the variants have data associated ...
- 19
0
votes
0
answers
100
views
Data modeling - strategy game - over-normalization?
I am trying to build a travian-like web browser game (static strategy game). I am currently working on my tables and data model. I don't have much experience with relational databases and I am ...
- 155
1
vote
1
answer
198
views
Database design for graph where nodes can be other graphs
How should I design a relational schema to store graph documents that can contain plain nodes (shapes, in the example below) as well as nodes that can reference other graph documents?
To visually ...
- 251
-1
votes
3
answers
400
views
Are there existing term(s) for a 1-1 child-parent table anti-pattern?
NOTE: Please don't respond by telling me that I probably don't understand what I am looking at. You can't possibly know that and it's wrong. Just don't answer if that's all you have to say.
I'm ...
- 27.8k
0
votes
0
answers
44
views
Cascading ACL to embedded objects
We have an object Root, which references many other objects. Which eventually is translated into FKs on the DB level. We came up with an ACL scheme where there's a separate permissions table with the ...
1
vote
1
answer
179
views
deciding where to put "formatting" logic - database or in application layer
We have an application that fetches user data from an Azure tenant via MS Graph. We need to distinguish between guests and regular members.
Guests come back with Usernames that look like this:
...
- 571
1
vote
1
answer
123
views
Is it s agood idea use as foreign key two fields instead of the primary key?
I have this two tables:
Documents(ID, IDTypeOfDocument, BigIntReference);
I have a documents table that has and ID that is an autonumeric bigint. Also I have categories, and also it has a big int as ...
- 543
6
votes
1
answer
5k
views
Best way to store quite large JSON objects?
I'm building an app where I need access to quite large reports about different publicly accessible URLs, JSON objects about 200kb to 500kB in size. Each user would generate hundreds of these reports ...
- 161
0
votes
3
answers
102
views
How to avoid data corruption with dual parent/child foreign keys
Imagine the following:
Persons table: (Id, FirstName, LastName)
PersonEmails table: (Id, PersonId, Address) (to allow a person to have multiple emails)
Contacts table: (Id, PersonId, UnsubscribeAll) (...
- 155
0
votes
1
answer
70
views
Secondary indexes vs Using elastic search
When does it make sense to put data in elastic search vs creating secondary indexing on Primary datastore?
Elastic search with another primary store
Pros:
Primary datastore can be optimised for read ...
- 134
1
vote
1
answer
357
views
How can I restore an entity from the database while keeping my entities database-independent?
Background
I'm currently working on a project using domain-driven design and Dapper as my ORM. The entities are naturally slightly different than the tables in which they are stored (e.g. _id is ...
- 225
-2
votes
1
answer
356
views
Storing a large number of user permissions [closed]
I am refactoring an old application which has about 200 user permissions.
Based on access permissions for the user, menu items for webpages will be Enabled or not.
Permissions are also checked on the ...
- 97
-2
votes
1
answer
481
views
Adding new column(s) to DB Table breaks existing queries [closed]
Dilemma: I am working in microservices (MS) architecture for a product with shared (PostgreSQL) DB between MSes and DB Views exposed as Data Access API between SW Components, written and maintained by ...
- 107
1
vote
1
answer
4k
views
SQL or NoSQL Database for a chat application?
currently I am working on a kind of "Chat" Application.
The app consists of "threads". Each of this threads consist of "subthreads" in wich a user can send a message (...
- 65
1
vote
1
answer
624
views
Exchanging data between two Microservices
I am facing this problem and would like to review my approach to it here.
A Client sends an HTTP request to Service A and is expecting an answer.
Service A is connected to DB A. When it receives a ...
- 167
0
votes
0
answers
142
views
How to design entities to keep up with the depth of JSON objects?
To better context see Rich Authorization Requests for OAuth2
As u can read here there are some common data field types like actions, locations etc.
So I have created C# entity for EF as below:
public ...
- 111
3
votes
2
answers
253
views
Preventing POST requests from being lost when database connection is lost
I am working on a project to stand up a central database to replace a current massive file-sharing/syncing system of physical log files and text configurations for the primary application I work on.
...
0
votes
1
answer
92
views
How to design a database wherein multiple tags(string) are to be associated with an id per user?
I have a similar situation to the user here How to design a database wherein multiple tags(string) are to be associated with an id?
However I'm designing a system where each association (between ...
- 1,033
2
votes
2
answers
324
views
How to store queryable 10-100MB BLOBs?
I have read several discussions about storing BLOBs in the database vs in an object storage. What I need in addition though is a functionality for querying these BLOBs. The BLOBs will be immutable ...
- 129