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,300
questions
-1
votes
0
answers
32
views
SQL: Using one shared Join table for multiple many-to-many relationships [closed]
I have these sql models: Albums, Collections, and Photos. Both Albums and Collections have many-to-many and one-to-many relationships with Photos and serve different application purposes. What are the ...
- 99
1
vote
4
answers
311
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...)
...
- 425
0
votes
2
answers
85
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
124
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
66
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 ...
- 145
1
vote
1
answer
137
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 ...
- 241
-1
votes
3
answers
357
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 ...
- 25.2k
0
votes
0
answers
31
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 ...
0
votes
0
answers
37
views
Using Final table and triggers to generate a appointment schedule or generate it on the fly? [duplicate]
I am making a appointment system for various businesses.
Each business provides us the following type of information (system already impemented):
Weekly schedule which day of week id open and close ...
1
vote
1
answer
148
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:
...
- 531
1
vote
1
answer
117
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 ...
- 425
3
votes
1
answer
1k
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 ...
- 131
0
votes
0
answers
46
views
Supabase Web App DB Design
I have the following Supabase db structure for my web app
Project -> Stage -> Logbook
A project can have many stages (stage has a foreign key for project)
A stage can have many logs (logs have ...
- 1
0
votes
3
answers
96
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
54
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 ...
- 111
1
vote
1
answer
237
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
173
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
142
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
0
votes
1
answer
1k
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 (...
- 55
0
votes
1
answer
164
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 ...
- 157
0
votes
0
answers
114
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
241
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
67
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
274
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
-2
votes
1
answer
265
views
Ho do I implement Database per Service pattern in Kubernetes?
Assuming I wanted to implement the Database per Service pattern or one of its related patterns like Private-Tables-, Schema- or Database-server-per-service (https://microservices.io/patterns/data/...
Domino
1
vote
0
answers
23
views
Optimised MongoDB design for access control at project/organisation level
I'm looking for the optimised approach of designing MongoDB database collections where there are three stakeholders-
A User (who accesses a portal).
An Organization (which can have multiple Projects)....
- 111
1
vote
3
answers
131
views
Triggers and network performance
There are many questions and responses on database triggers, however I have not found any that touch on network performance specifically, in the matter of benefits (or otherwise) of triggers.
I have ...
- 119
1
vote
2
answers
223
views
Is it a good idea to design a database based on the API response data types?
I am trying to design my database, where i have to save some response data given from an external api as a webhook.
This api will return me as well “referenceId” as an identifier, which i will use ...
Lackeeee
1
vote
1
answer
75
views
SQL - store "like" counts as separate column or infer from a query?
Say I have a feature in a web app where users can create a post and like it. In the frontend the user should see the number of likes a post has.
I could store the data two ways:
1. Option 1: A small ...
- 151
1
vote
1
answer
88
views
Modeling properties of related entities
I have the following entity relationship.
A {state: ON | OFF} => B {state: ON | OFF} => C {state: ON | OFF}
So, in this case, B is a child of A, and C is a child of B. Moreover, they all share ...
- 173
1
vote
3
answers
257
views
How is double-spending technically prevented in traditional banking? [closed]
In contrast to bitcoin:
How is double-spending technically prevented in traditional banking? Do database transactions have to conform to the ACID property in order to prevent double-spending?
- 129
9
votes
4
answers
4k
views
How to store a fixed length array in a database
I have a mysql database in which I have drafts, each of which contains exactly 24 players the order of which matters. I am conflicted between having a drafts table with 24 extra columns for each ...
- 101
1
vote
1
answer
107
views
Database schema advice : multiple tables with the shared columns in postgresql
Let's pretend I'm making a basic twitter clone. We could imagine our database is set up as the following:
1. Table 1 - contains user info {username, password}
2. Table 2 - contains session info {...
- 151
1
vote
1
answer
165
views
Representing database tables as objects
I'm building an address book API, where a user can input multiple contact names, phone numbers, and addresses.
My database structure would be as follows:
create table user (
id int not null primary ...
- 19
1
vote
2
answers
2k
views
When to use an array of objects or an object of objects in MongoDB Design?
I have a question about the general guidance of how to properly mongodb design:
Scenario: I have many collection of objects, all uniquely identified by their contract_address, and i want to be able to ...
- 13
0
votes
0
answers
44
views
How to handle multi-region writes for a joint user registration?
UserA and UserB are signing up for some joint service together. They both have to independently agree to T&Cs before sign-up is complete. Once complete they both receive an email to sign-in.
A 1 ...
0
votes
2
answers
77
views
Database many to many insert pattern
Suppose I have a DB schema with two tables, a Student table and a Subject table. The relationship between these is many-to-many, so I also have a StudentSubject join table.
Now suppose that, ...
- 109
1
vote
1
answer
345
views
Is DynamoDB optimistic locking with one version attribute for each field in an item a valid design pattern?
It's common to implement optimistic concurrency control in DynamoDB by giving each item in the database a top-level "version" attribute and only allowing an update of an item to succeed if ...
- 111
-1
votes
1
answer
49
views
How to merge a lot of smaller tables with single-column differences into a one bigger table?
I have a multiple same tables (14 for now), with a only foreignKey column difference. Some of them are:
DatasetGraphTable
+-------------------------+----------------------+--------------------+
| ...
- 109
-1
votes
1
answer
32
views
How to model multiple requirement scenario
I want to store and analyze university course requirements. Lets say, students to register for ABC123 course following requirements should satisfy.
Must pass CBA321
Should be taken with XYZ123
Must ...
11
votes
10
answers
5k
views
Is there any benefit to a separate table that is one-to-one with the primary data table?
I've inherited a system with an Oracle relational database with a couple of tables modeled like I've sketched below, where there's an entire child table that only stores a single status code in a one-...
- 366
1
vote
0
answers
89
views
Efficient way to implement hierarchical inheritance in SQL?
I am working in a database that has a hierarchy of Companies (From location-level all the way up to Top-level parent company). I am designing a system within that to provide a "subscription" ...
- 11
2
votes
3
answers
185
views
User's comment field on a row in relational databases
Suppose that we have a SQL relational database for, let's say, asset management system. It uses a table of assets (1 row per one real-world object). There can be various metadata etc. To allow the ...
- 191
1
vote
1
answer
89
views
Aggregating embedded documents in MongoDB
I'm designing a mongo database and I have a dilemma if I should go for normalization.
Let's say that there is a database with images and descriptions. Many alternative descriptions can belong to one ...
- 127
-1
votes
2
answers
126
views
REST API vs upserts and "updeletes"
I am building an API that (I am deliberately simplifying the schema below to only focus on what is questionable):
I have a table that roughly looks like this:
CREATE TABLE IF NOT EXISTS some_table
(
...
- 115
0
votes
1
answer
67
views
Should the intentional absence of a value rather be represented by value-convention or through explicit flag?
Following context:
in a set of metadata that contains a lot of possible fields there exists at least one field (possibly more in the future) that can be empty by intention but realistically will be ...
- 103
1
vote
1
answer
317
views
Foreign key vs string
I have to create a table to map some strings that are my input, to the result value that is a value that I can choose, like an id (or string).
So, to make an example, I have an excel file made of a ...
- 479
-1
votes
1
answer
46
views
Picking data store for Location Tracking Based Application
I have one design problem for one of my projects based on location tracking. The scenario is I have multiple devices(for some business) all over the city and have GPS installed inside, the device is ...
0
votes
1
answer
111
views
Mixing static referred and dynamic data in database
I’m making an inventory for a game using nosql (mongo). I made some ItemType records (contains common data like icon, name, weight) and some ItemInstance records (representing specific items, ...
- 111
0
votes
5
answers
138
views
Metadata persisted or created at runtime?
Let us say I have an entity House that I want to store on a database. The House has an attribute yearBuild. If the house was build before 1990 its status should be 'ToBeSelled'. Now I have two ways to ...
- 1,001