24,211 questions
0
votes
0
answers
90
views
How to manage database where identifiers are split across multiple columns? [closed]
A legacy project I am working on has a recurring entity that can only be uniquely identified using not one, but four pieces of information.
If we take a "Person" entity as an analogy, there ...
- 324
-2
votes
0
answers
55
views
Expanding SQL queries with WASM [closed]
I'm building a database and I just introduced a very hacky feature about expanding SQL queries with WASM. For now I just implemented filter queries or computed field queries, basically it works like ...
- 2,578
Best practices
0
votes
4
replies
45
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
158
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
43
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
128
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
104
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
104
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
120
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
1
answer
63
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
144
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
70
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
84
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
125
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
162
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
73
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
60
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
36
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
97
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,418
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
172
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,637
0
votes
1
answer
96
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
174
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
90
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
110
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
65
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
202
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,...
- 381
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,916
0
votes
2
answers
211
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
87
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
39
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
288
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,181
1
vote
3
answers
166
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
40
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
104
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
673
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
77
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
77
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
47
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
65
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,540
1
vote
1
answer
61
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
134
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
72
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