24,210 questions
Best practices
0
votes
3
replies
39
views
Feedback on ERD normalization for school project for a service to match users based their preferences, and how to model verification status in ERD?
What is feedback on the normalisation of my ER diagram which I designed for my school project for a service to match users based on their preferences?
The ERD is supposed to show no transitive ...
Best practices
0
votes
2
replies
36
views
Updates with temporal foreign keys
I'm playing around with Postgres 18 temporal keys. At the moment, I have this schema:
CREATE EXTENSION btree_gist;
CREATE TABLE customer
(
id integer NOT NULL,
valid_at daterange ...
- 307
Best practices
0
votes
4
replies
38
views
Postgres: Best design pattern for "Exclusive Arc" (Polymorphic) relationships? (Nullable FKs vs. alternatives)
I am designing a database schema in PostgreSQL for a notification system. I have several distinct "parent" entities that can trigger a notification log:
order
invoice
shipment
...
Advice
2
votes
3
replies
57
views
Ensuring adjacent ranges in PostgreSQL
I am creating a database with temporal validity using date ranges in PostgreSQL 18.1. I want to place constraints which force the date ranges to be non-overlapping and adjacent (= there are no gaps).
...
- 307
Best practices
1
vote
4
replies
54
views
How do I differentiate each device so that each one has its own unique refresh token linked to the same account?
I am designing a mobile application with a user login system.
I am going to implement refresh tokens in order to allow for users to only have to log in once every 30 days.
How do I differentiate each ...
- 91
Best practices
0
votes
6
replies
70
views
How do I enforce that a sequence column does not have random integer values inserted?
Table event records an instance of an event.
Some events are repeating and should be referenced as a batch. I do not want to create a separate table as event_batch or something like this, as I just ...
- 4,028
Best practices
1
vote
3
replies
42
views
Should resume skills be stored as JSON, BLOB, or normalized tables for analytics and search performance?
Should resume skills be stored as JSON, BLOB, or normalized tables for analytics and search performance?
I'm building a resume analyzer that extracts skills and stores them for analytics. Currently ...
- 1
Advice
0
votes
6
replies
91
views
Where should a beginner start with System Design and Pattern Recognition?
I am a software developer and I’m starting to learn System Design and Pattern Recognition concepts.
I want to understand:
how to approach system design problems step by step.
what fundamentals I ...
Advice
0
votes
2
replies
61
views
What are problems with my reasoning that a WAL (write-ahead log) is redundant for my TSDB (time-series DB)?
I am building a time series database in C++. Changes to record sets are restricted to appends. This is not a production system, rather for learning.
The motivation and purpose for a WAL (write-ahead ...
- 1
1
vote
1
answer
89
views
How do I version translations while keeping relationships?
Some part of a post has translations:
CREATE TABLE "posts" (
"id" int4 NOT NULL,
"title" varchar,
PRIMARY KEY ("id")
);
CREATE TABLE "...
- 4,424
Best practices
0
votes
4
replies
52
views
Dynamic field search in Spanner DB
We're building a SaaS platform for which we need to design a data model which could accompany domain specific data. We don't know the structure of domain specific data. So these are the evaluated ...
- 323
Best practices
0
votes
5
replies
167
views
How to condense multiple boolean columns into one column in laravel?
How do I design a scalable user badges system without creating many boolean columns?
I'm building a badge system where users earn specific badges by completing certain tasks.
Schema::create('...
Advice
0
votes
3
replies
46
views
Review FMCG business inventory system DB design normalization and ERD
My case study project inventory system tracks stock and updates stock after every sale of a FMCG (Fast-moving consumer goods) business. Since it is FMCG, they get stock by consignment and the database ...
Best practices
0
votes
2
replies
130
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 ...
- 615
Best practices
0
votes
5
replies
105
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 ...
- 10
Best practices
1
vote
1
replies
106
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
121
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
-1
votes
1
answer
69
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 ...
- 51
3
votes
1
answer
153
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 ...
- 496
1
vote
2
answers
71
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
86
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
127
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,804
0
votes
2
answers
165
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
85
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
62
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
104
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
37
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
104
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,791
0
votes
1
answer
67
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
176
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,657
0
votes
1
answer
104
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
194
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
91
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
112
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
116
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
68
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
203
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,...
- 435
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,934
0
votes
2
answers
220
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
89
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
40
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
312
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,191
1
vote
3
answers
180
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
53
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
111
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
78
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
743
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.9k
-2
votes
2
answers
79
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,379
-1
votes
1
answer
72
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
81
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