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.

Filter by
Sorted by
Tagged with
-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 ...
ddolce's user avatar
  • 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...) ...
Álvaro García's user avatar
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 &...
AminMal's user avatar
  • 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 ...
yoshke's user avatar
  • 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 ...
Pierre-Alexandre's user avatar
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 ...
rink.attendant.6's user avatar
-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 ...
JimmyJames's user avatar
  • 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 ...
Stanislav Bashkyrtsev's user avatar
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 ...
Dimitrios Desyllas's user avatar
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: ...
dot's user avatar
  • 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 ...
Álvaro García's user avatar
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 ...
any_h's user avatar
  • 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 ...
Bone r's user avatar
  • 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) (...
Mr. TA's user avatar
  • 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 ...
best wishes's user avatar
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 ...
JansthcirlU's user avatar
-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 ...
Daniel Gee's user avatar
-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 ...
ALZ's user avatar
  • 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 (...
Vito's user avatar
  • 55
0 votes
1 answer
165 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 ...
Solidak's user avatar
  • 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 ...
Szyszka947's user avatar
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. ...
Benjamin McKay's user avatar
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 ...
AncientSwordRage's user avatar
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 ...
Ivaylo Toskov's user avatar
-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/...
user avatar
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)....
Shashank Agrawal's user avatar
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 ...
Sunil Gupta's user avatar
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 ...
user avatar
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 ...
Leftover Salad's user avatar
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 ...
salparadise's user avatar
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?
Dawid's user avatar
  • 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 ...
RobbyG's user avatar
  • 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 {...
Leftover Salad's user avatar
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 ...
tntenigma's user avatar
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 ...
Askar's user avatar
  • 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 ...
mockitodorito's user avatar
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, ...
cluelessatthis's user avatar
1 vote
1 answer
346 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 ...
fblundun's user avatar
  • 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 +-------------------------+----------------------+--------------------+ | ...
Lube's user avatar
  • 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 ...
Sachith Muhandiram's user avatar
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-...
Kaypro II's user avatar
  • 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" ...
user2563087's user avatar
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 ...
jiwopene's user avatar
  • 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 ...
Karol Borkowski's user avatar
-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 ( ...
Don Draper's user avatar
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 ...
Wolfone's user avatar
  • 103
1 vote
1 answer
318 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 ...
Accollativo's user avatar
-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 ...
Praveen kumar's user avatar
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, ...
Bohdan Bessonov's user avatar
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 ...
Cap Barracudas's user avatar

1
2 3 4 5
26