Database Design

Real-world analogy

What is it?

Database design is choosing tables, keys, types and constraints so the data stays correct and queries stay simple. Normalization splits data so each fact lives in one place, which prevents the same value being updated in three tables. Constraints let the database reject impossible rows, whatever code or script tries to write them. Good design is mostly boring: clear keys, the right types and rules that the database enforces itself.

Real-world relevance

The expensive mistakes are quiet. Prices stored as float drift by a cent and the invoices stop matching. An "email must be unique" rule exists only in C# and two quick sign-ups create duplicate accounts. A random GUID clustered key makes inserts slow as the table grows. Each one is cheap to prevent on day one and painful to repair across a table with millions of rows.

Key points

• Identity vs GUID keys — Identity integers are small and ordered. GUIDs can be created anywhere. Random ones scatter new rows across a clustered index in SQL Server and cause page splits.
• Sequential GUIDs help — EF Core with SQL Server generates sequential GUIDs by default for a Guid key. PostgreSQL tables are not clustered, so random keys hurt less.
• Normalization: one fact, one place — First form: one value per cell, no Phone1, Phone2 columns. Second: every non-key column needs the whole key, so ProductName leaves an (OrderId, ProductId) table. Third: no column depends on another non-key column, so CustomerCity belongs in Customers, not Orders.
• Denormalize on purpose — Store Total on Orders so list pages do not sum the lines every time. Do it only for measured read speed, and keep the copy in sync in the same transaction.
• Constraints are the last defence — NOT NULL, UNIQUE, CHECK and foreign keys stop bad data from any source. Code checks fail when two requests race or someone runs a script.
• Pick the right data types — Use decimal for money, because float cannot store 0.1 exactly. Store timestamps in UTC: datetime2 in SQL Server, timestamptz in PostgreSQL.
• Soft delete has a price — An IsDeleted flag keeps history, but every query must filter it, or rely on EF Core's HasQueryFilter. Unique rules also clash with deleted rows unless the unique index is filtered (partial in PostgreSQL).
• Many-to-many needs a join table — A table with two foreign keys, like OrderItems, links the two sides. A composite primary key stops duplicate pairs.
• SQL Server vs PostgreSQL — PostgreSQL folds unquoted names to lowercase, so a table created as "Orders" must always be quoted. SQL Server quotes with [brackets] and usually ignores case. SQL Server uses nvarchar and rowversion; PostgreSQL uses text and the xmin system column.

Code example

-- SQL Server (T-SQL)
CREATE TABLE dbo.Customers (
    Id    int IDENTITY(1,1) PRIMARY KEY,
    Email nvarchar(254) NOT NULL UNIQUE,
    Name  nvarchar(100) NOT NULL
);

CREATE TABLE dbo.Products (
    Id   int IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(100) NOT NULL
);

CREATE TABLE dbo.Orders (
    Id         int IDENTITY(1,1) PRIMARY KEY,
    CustomerId int NOT NULL REFERENCES dbo.Customers (Id),
    Status     varchar(20) NOT NULL CHECK (Status IN ('New', 'Paid', 'Shipped')),
    CreatedAt  datetime2 NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Join table: many orders have many products
CREATE TABLE dbo.OrderItems (
    OrderId   int NOT NULL REFERENCES dbo.Orders (Id) ON DELETE CASCADE,
    ProductId int NOT NULL REFERENCES dbo.Products (Id),
    Quantity  int NOT NULL CHECK (Quantity > 0),
    UnitPrice decimal(10, 2) NOT NULL CHECK (UnitPrice >= 0),   -- price at the time of sale
    PRIMARY KEY (OrderId, ProductId)
);

-- Foreign keys are not indexed automatically; this one serves "orders of a customer"
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);

Line-by-line walkthrough

1. 1. Customers has an identity key. Email is NOT NULL and UNIQUE, so the database itself blocks duplicates.
2. 2. Products is a small lookup table with its own key.
3. 3. Orders points to a customer with a foreign key. The CHECK allows only three status values, and CreatedAt defaults to UTC time.
4. 4. OrderItems is the join table between orders and products. Its composite primary key stops the same product twice in one order.
5. 5. Quantity and UnitPrice have CHECK rules, and UnitPrice is decimal(10, 2) so money does not drift.
6. 6. The final index covers the foreign key on Orders, because the database does not add one by itself.

Spot the bug

CREATE TABLE dbo.Users (
    Id    int IDENTITY(1,1) PRIMARY KEY,
    Email nvarchar(254) NOT NULL
);

-- Application code runs before each insert:
-- if (!await db.Users.AnyAsync(u => u.Email == email)) { insert the user }

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Keys in EF Core (Microsoft Learn)
• PostgreSQL: Constraints (PostgreSQL Documentation)
• Indexes in EF Core (Microsoft Learn)