24,213 questions
Best practices
0
votes
2
replies
116
views
How to model and persist entities whose state changes over time
I’m designing a domain model where some entities evolve over time, and I need to keep a historical record of their state that can be retrieved later.
A simplified example:
A Credit Line represents a ...
- 605
Best practices
0
votes
5
replies
95
views
How to handle a “ghost” product in a SellDetails table without breaking normalization?
I'm developing a desktop application for gym management. One feature is a store for selling memberships and products.
My client sometimes wants to sell something that is not in the inventory or ...
- 11
Best practices
1
vote
1
replies
101
views
Should I separate a 10+ TB SQL Server data warehouse into multiple databases or keep it in one with multiple schemas?
Our team manages a SQL Server 2019 data warehouse that’s grown to over 10 TB.
It currently uses a single database with multiple schemas (clinical, financial, hr, operations, staging, etc.).
Our DBA is ...
- 68
Best practices
3
votes
1
replies
105
views
Architecture design for multilingual descriptions for several classes
I am creating an E-Commerce app. It has Category and Product classes. Both have multilingual descriptions, so, tables with title, description, meta-*, etc. and unique key (id, language_id).
To add ...
- 178
0
votes
0
answers
104
views
Create a column to have a foreign key to the union of two other columns [duplicate]
I have a table
CREATE TABLE Person (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
home CHAR(2) NOT NULL
);
where I would like the values of home to be either a country code or a ...
- 9
0
votes
1
answer
61
views
Referencing by SHA256 unique documentID vs. referencing by the field that generated the unique SHA256 unique documentID [closed]
I’ve been working on a React/Firebase app for the past 10 months, and things have been smooth so far. Recently, I decided to do some refactoring.
I switched from using user-generated fields as unique ...
- 53
3
votes
1
answer
135
views
How do I enforce uniqueness across parent and child tables without denormalizing my data?
I have two tables in Postgres with a parent/child relationship:
CREATE TABLE table_a (
id SERIAL PRIMARY KEY,
type TEXT NOT NULL,
rundate DATE NOT NULL
);
CREATE TABLE table_b (
id ...
- 486
1
vote
2
answers
68
views
Can a 1:N and an M:N relationship coexist between two entities?
We have entities Teacher and Student; there are two relationships between them, supervision and teaching:
Supervision is one-to-many, but teaching is many-to-many, which is a problem. To fix it, I ...
- 19
0
votes
1
answer
74
views
How do I preserve data integrity with a linking table in PostgreSQL?
I have these tables in my PostgreSQL database:
CREATE TABLE companies (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE
);
-- Employees belong to one company
CREATE TABLE employees (
...
-1
votes
1
answer
120
views
Get the column NAME as a foreign key
I have a table Languages:
ID | ISO639 | NAME
---+--------+---------
1 | EN | English
2 | DE | Deutsch
3 | TR | Türkçe
(etc.)
I have another table Headlines:
ID | EN | DE | ...
- 1,788
0
votes
2
answers
151
views
Insert record into many-to-many relationship [duplicate]
I want to create a database for my blog website where I have many posts and categories. A post can have many categories and a category can have many posts. I couldn't figure out how to implement it in ...
-1
votes
1
answer
69
views
PostgreSQL add column to Partition (for Table Inheritance)
Why isn't it possible to add a column to a partition?
From my point of view, the partitioning feature introduced in PostgreSQL is much more useful than table inheritance.
Table inheritance shoots at ...
- 1,224
0
votes
1
answer
55
views
good performance representing user-defined data in both hierarchical and flat view
I have an application that represents user-defined data in two views:
A hierarchical view (parent-child nodes)
A flat view (a table where all attributes are listed in a row)
In PostgreSQL it handles ...
- 21
-1
votes
1
answer
102
views
Normalize Table2 where one person can only have one device in Table1, but there are many different colors for the same device in Table2 [closed]
Table1 has roughly 50k rows, like this:
Id
Name
Dob
Device
1
Bill
09/30/1987
bira1
2
Kayle
06/12/1967
kale2
3
Gill
05/23/1976
bira1
4
Kane
01/30/1963
bira3
5
Hena
02/12/1978
kale1
Table2 has 200 rows:
...
- 207
0
votes
0
answers
32
views
How to properly structure a time-series schema for sensor data in GridDB Cloud for efficient querying?
I'm designing a time-series application using GridDB Cloud to store sensor data from IoT devices. Each device sends temperature and humidity readings every 10 seconds.
device_id (string)
temperature (...
- 29
0
votes
1
answer
91
views
have SQLAlchemy or SQLModel define a database schema starting from an example of the object I would like to persist
I would like to save objects like this to a local database.
The object is a dictionary with various entries.
For instance, the first entries are like:
"id": "https://openalex.org/...
- 5,364
0
votes
1
answer
63
views
Database level design for undo operation
I have an entity "MyEntity" which has some fields - "StartDate", "EndDate", "Color".
It can have multiple records, and each record is sometimes updated. I need ...
- 235
-1
votes
2
answers
164
views
What approach would be best for SQL dynamically-ordered lists (i.e. music playlists)? [closed]
I am designing a database for a music player. I am already using SQLite to store music tracks in a table called tracks, containing a unique id.
I need to add very standard playlist functionality: ...
- 1,627
0
votes
1
answer
83
views
optimized multi-tenant data model to support tenants without increasing the storage, using Kafka as a message queue
I'm working on a SAAS product where we have a multi-tenant data model with multiple fields that are specific to tenants. I am using Kafka as a message queue and PostgreSQL as datastore.
The way I ...
- 179
0
votes
0
answers
163
views
Inserting data into a Postgres database with Trino causes an error io.trino.spi.type.IntegerType
I have a Trino that reads Parquet (which was converted from JSON) and wants to insert that data into a PostgreSQL database.
When defining the Trino schema and creating a table, it is ok. But after ...
- 775
-1
votes
1
answer
89
views
When should double validation be implemented with both the database and the backend? [closed]
I'm trying to figure out how to balance security, performance, and best practices in the validation process of the data of a REST API and its interaction with the database.
Say you have a book table ...
3
votes
3
answers
108
views
Which column to index in multicolumn indexing?
UserID
First
Middle
Last
Type
CreatedAt
123
John
Henry
Doe
Mage
03-28-2025
Let's say I have the following table above. I would like to create index to help speed up my queries.
All queries would like ...
- 41
1
vote
0
answers
114
views
dimensional modeling from two different source systems for the same business process?
I have two transactional systems that is used for the same operational business activity: purchase order transactions. One system is legacy and the other is a new modern one. They have different ...
- 801
0
votes
0
answers
64
views
FIFO inventory accounting for blockchain data in SQL
I'm trying to implement real-time analytics on aggregated blockchain data. My database stores swap events (SwapEvent table) with information about tokens traded, amounts, and USD values. Currently, I'...
- 1
0
votes
1
answer
190
views
Does "tuples are not necessarily distinct" imply they are equal? How do I show whether this multivalued dependency holds in the example table?
From the book Fundamentals of Database Systems (7th edition) by Elmasri et al., pages 475-476:
A multivalued dependency [MVD] X ↠ Y specified on relation schema R, where X and Y are both subsets of R,...
- 365
0
votes
1
answer
44
views
Is there a mechanism to set different priorities to ensure that access to postgresql database in golang? [closed]
The server I developed with golang is divided into two categories when accessing the database. One category is data with redis cache, which is generated very frequently, but inserted into the database ...
- 1,896
0
votes
2
answers
200
views
SQL Server table scan when using SELECT *
I am working with SQL Server 2019 and have a TVF that retrieves products based on a list of categories. However, I noticed that when I use SELECT *, the query results in a table scan, whereas ...
- 1,006
0
votes
2
answers
81
views
Integer PK but a GUID as FK
In our current PostgreSQL DB, primary keys are Ints. However, in almost all of the communications between services another candidate key is used, a GUID. While I am not the original author of the ...
- 35
0
votes
0
answers
37
views
Special many to many relation in SQLAlchemy
I am trying to use sqlalchemy to model a database consisting of two classes A and B. The B class has two fields: B.a (1 to n) and B.alist (n to n).
I am trying to follow the Setting Bi-Directional ...
- 3,463
1
vote
2
answers
256
views
How should I paginate a SQL query where the rows have no natural ordering?
I've inherited a code base with an associated database schema.
The schema has numeric, auto-incrementing primary keys. It does pagination with queries like this:
WITH params AS
(
SELECT id
...
- 8,161
1
vote
3
answers
146
views
Oracle Sequence Gaps: Are Skipped Values Ever Reused
I use an Oracle sequence of the form:
CREATE SEQUENCE MY_SEQUENCE INCREMENT BY 1 START WITH 1000000000 MAXVALUE 9999999999 MINVALUE 1000000001
NOCYCLE CACHE 20 NOORDER;
This sequence is used in an ...
0
votes
1
answer
38
views
How to efficiently list recently active group chats for users without fan-out writes in 1000+ member groups?
I'm using a two-collection approach (userChats and chats) for quickly accessing a user's group chats as opposed to the use of an extremely long participant array of user IDs. However, I am struggling ...
- 111
-1
votes
1
answer
102
views
How to efficiently store and search large lists of user preferences in a SQL database?
I’m working on a web application where users can select multiple preferences (e.g., favorite foods, music genres, hobbies, etc.). A user can have many preferences, and I need to store this information ...
-1
votes
1
answer
74
views
How can I model resource exchange processes?
I'm trying to model the economy of a resource management game. Processes turn one type of resource into another, at varying rates.
For example, process A turns 1x item A into 1x item B. That's simple, ...
- 21
1
vote
1
answer
623
views
Create DuckDB table with primary key from parquet
I am trying to set up a simple but large DuckDB database with a single column of unique values as read from a parquet file. For faster inference of single-point existence checking (WHERE id = test_id),...
- 28.8k
-2
votes
2
answers
74
views
SQL table design with foreign key [closed]
I have two tables:
Product
Columns: Product ID (PK), Product Name
Order
Columns: Order ID (PK), Order Name
I would like to store which Product Id for each order.
Is it better to create another table:
...
- 1,621
-1
votes
1
answer
66
views
In ER diagram, is order and cart relationship are weak entity relationship?
In an entity relationship (ER) diagram, is the relationship between Order and Cart considered a weak entity relationship? Because order depends on the cart.
I analyzed the relationship between Order ...
-2
votes
1
answer
71
views
Meaning of hashtag in a relational schema
What does the # mean when placed before an attribute in a relational database schema?
Umbrella(ID,Lido, Cost, Type)
Lido(ID, Name, #Spots, Manager)
Manager(FiscalCode, Name, Surname)
Client(#Card, ...
- 21
-2
votes
1
answer
59
views
Relational table design with same type in a many-to-many relationship
I have a table called Device, and another table called Action. I link them with my junction table DeviceActions on both PKs.
Here's a simplified ERD model:
If each action is only recorded once for ...
- 512
-2
votes
2
answers
40
views
connect these tables with many-to-many relationship within a PowerBI report
I have a database about bicycles. One table is for the construction of the whole bike and one is for the construction of each wheel. There can be multiple rows for each bike since it can be other ...
1
vote
0
answers
13
views
Whether can be defined foreign key using or in typeORM?
When design the tables I get doubt (below is the pseudo-code) :
Node Table {
id: number,
label: string
}
Comb Table {
id: number,
label: string
}
Link Table {
source: // Here it can be an ...
- 129
0
votes
1
answer
62
views
Ensuring consistent common reference among two tables
SQLite schema:
CREATE TABLE Lots (
Id INTEGER PRIMARY KEY
);
CREATE TABLE Parts (
Id INTEGER PRIMARY KEY,
LotId INTEGER NOT NULL REFERENCES Lots
);
CREATE TABLE Inspections (
Id ...
- 5,418
1
vote
1
answer
53
views
How to track changes to a multifield record in DB
I have a table that keep records that can be added and then updated by a user. Record has many fields. Some fields can be logically grouped, some have similar or the same data type. I need to keep in ...
- 19.4k
0
votes
0
answers
120
views
Modelling auto parts equivalency in a database
I am developing an auto parts quoting and pricing application for a company that sells auto parts. The client wants the application to handle their internal daily operations, such as calculating parts ...
-1
votes
1
answer
93
views
How do I simplify this model with a double reference?
My CRUD application's data models:
What is the proper way to set up the FilmGroup model? It has a one-to-many relationship with Film and a one-to-one relationship with FilmOrder.
My models for the ...
- 2,107
0
votes
1
answer
69
views
stock management - determine opened state automatically
I have a database for animal rescues which has a medication section. I'd like to have the stock automatically deduct when it is used. The challenge is the opened bottles. For something like an ...
- 35
3
votes
1
answer
112
views
Postgres gist constraint difference between tsrange datatype and function
For my database design, I have to options for storing timestamp ranges and preventing overlaps using a gist exclude constraint:
Option 1
create table example (
id bigserial primary key,
kind_id ...
- 3,152
0
votes
2
answers
99
views
Facing the problem of uploading the member ID while uploading the data to multiple tables through a single form/click
I have a form with multiple fields to upload data and fetch the member's data to the front end. In the front end, some sections have multiple points so I have created separate tables for them. To ...
user21718837
-1
votes
1
answer
44
views
How should the device store save the groupId if you have a new device and an account? [closed]
App users are stored in collection users and groups in collection groups. At a high level a user is part of one group. When you log in you are asked to either join or create a group.
I store the ...
- 957
-2
votes
1
answer
50
views
Use redis list or custom list? [closed]
I want to have a state and list for each identifier , my question is should i implement redis hash with state and list(custom list) for each identifier , or build a hash for state and list for queue ...
- 358