24,210 questions
-2
votes
1
answer
70
views
Online clothing store: Which tables are well normalized and which need to be normalized? [closed]
The project is a database for an online clothing store.
Which tables are well normalized and which need to be normalized?
DB schema of all the tables before doing any normalization:
https://i.sstatic....
0
votes
0
answers
47
views
How to handle contention at scale?
We’re building a system where an offer can be redeemed until a global limit is reached.
For example, an offer may allow a maximum total redeemable amount (e.g., 10M), or/and only allow each user to ...
- 657
Best practices
0
votes
2
replies
122
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
97
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
103
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
115
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
114
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
138
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
69
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
82
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
121
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
153
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
70
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
59
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