End to End: Designing the Bookstore Schema from Scratch and a Schema Review Checklist
A good schema is one where the rules in the requirement sentences are written as constraints; most of those rules hide between the sentences. From pre-order, coupon and stock reservation requirements we go to an entity list, a Mermaid ER diagram, a BCNF check with closure and SQL Server DDL, test the constraints against business rules, keep descriptions in the schema, review an EF Core migration and produce a schema review checklist.
Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10), the C# example with .NET 10 and Entity Framework Core (EF Core) 10.0.15, and the example in the PostgreSQL note on PostgreSQL 18.6; the Mermaid diagram was verified by rendering it with Mermaid 12.3.0.
Guess First: How Many Tables Do Three Requirements Make?
The bookshop's product team wants three new features: pre-orders for books not yet released, discount coupons, and a short hold on books added to the cart. The note from the meeting is three lines. Guess: how many new tables, new columns and constraints do these three lines add to the Bookstore schema? And how many of them are visible on the first read?
In this post we apply every step of the series to these three requirements in order: extracting entities and rules from sentences, the ER (Entity-Relationship) diagram, dependencies and the normal form check, the physical DDL (Data Definition Language), keeping descriptions in the schema, and reviewing the migration EF Core generates. In the end we will have four new tables, one change each in two existing tables, a view, twenty-eight constraints and two indexes; most of the constraints are not written explicitly in the requirement sentences but come out from between them. The examples run in the dm_12 database, which holds the Bookstore schema of the SQL Temelleri series.
Requirements and the Entity List
We start with the method from the 1st post of the series: reading every sentence as nouns (entity candidates), verbs (relationships) and rules (constraint candidates). When we went through the meeting note with the product team, the sentences became these:
Pre-orders: "A pre-order can be placed for a book whose release date is in the future. The price is fixed when the pre-order is placed. When the book is released, the pre-order becomes a real order; the customer can cancel it if they want. A customer has a single waiting pre-order for the same book."
Coupons: "Every coupon has a unique code. The discount is a percentage or a fixed amount. A coupon is valid within a date range and may have a total usage limit. A customer uses a coupon once. At most one coupon is used per order."
Reservations: "A book added to the cart is reserved for 15 minutes. The reserved quantity is not taken out of stock, but it lowers the sellable stock. An expired reservation is invalid on its own."
Entities: pre_orders (something with its own life cycle; it waits, converts or gets cancelled), coupons, coupon_redemptions (the use of a coupon on an order; a fact with a date, an amount and a customer, that is, an associative entity) and stock_reservations. Existing entities are touched too: a book needs an "available for sale from" date. Access patterns were noted as well: the cart screen reads the sellable stock for every book, the payment step validates and redeems the coupon, and a nightly job converts pre-orders of released books into orders.
The ER Diagram: Mermaid in Code
In the 2nd post of the series we saw how to keep the ER diagram in code with Mermaid and make it diffable. The new entities and their relationships with existing tables:
erDiagram
customers ||--o{ orders : places
customers ||--o{ pre_orders : places
customers ||--o{ stock_reservations : holds
books ||--o{ pre_orders : "is pre-ordered in"
books ||--|| stock_items : "is stocked as"
stock_items ||--o{ stock_reservations : "is reserved by"
orders |o--o{ pre_orders : "is converted from"
coupons ||--o{ coupon_redemptions : "is redeemed in"
orders ||--o| coupon_redemptions : "uses at most one"
coupons {
int coupon_id PK
varchar code UK
varchar discount_type
decimal discount_value
datetime2 valid_from
datetime2 valid_to
int max_redemptions "NULL = unlimited"
int redemption_count
}
coupon_redemptions {
int order_id PK, FK
int customer_id FK
int coupon_id FK
decimal discount_amount
}
pre_orders {
int pre_order_id PK
int customer_id FK
int book_id FK
decimal unit_price "snapshot"
varchar status
int order_id FK "NULL until converted"
}
stock_reservations {
bigint reservation_id PK
int book_id FK
int customer_id FK
int quantity
datetime2 expires_at
}Two decisions in the diagram need attention. orders ||--o| coupon_redemptions: an order has zero or one coupon use, so the key of coupon_redemptions can be order_id itself; it is the 1-1 shape from the 3rd post, where the foreign key on the optional side is the primary key. orders |o--o{ pre_orders: a pre-order becomes at most one order, an order can be built from several pre-orders, and a pre-order that has not converted has no order.
Dependencies and the Normal Form Check
The two rules of coupon_redemptions are two separate dependencies: "at most one coupon per order" means order_id → coupon_id, "once per customer" means {coupon_id, customer_id} → order_id. Let's check with the closure procedure from the 5th post:
CREATE TABLE dbo.fds (
fd_id INT NOT NULL,
side CHAR(3) NOT NULL CHECK (side IN ('lhs', 'rhs')),
attr VARCHAR(30) NOT NULL
);
INSERT INTO dbo.fds (fd_id, side, attr) VALUES
(1, 'lhs', 'order_id'), (1, 'rhs', 'customer_id'), (1, 'rhs', 'coupon_id'),
(1, 'rhs', 'discount_amount'), (1, 'rhs', 'redeemed_at'),
(2, 'lhs', 'coupon_id'), (2, 'lhs', 'customer_id'), (2, 'rhs', 'order_id');EXEC dbo.attribute_closure 'order_id';
EXEC dbo.attribute_closure 'coupon_id, customer_id';step attributes
---- ----------------------------------------------------
0 order_id
1 coupon_id, customer_id, discount_amount, redeemed_at
step attributes
---- ----------------------------
0 coupon_id, customer_id
1 order_id
2 discount_amount, redeemed_atThere are two candidate keys: {order_id} and {coupon_id, customer_id}. Since the determinant of both dependencies is a candidate key, the table is in BCNF (Boyce-Codd Normal Form). But one thing stands out: customer_id is also in the orders table, and order_id → customer_id holds there too. So the column is a copy across tables. Why keep it? Because it is the way to write the "once per customer" rule as a UNIQUE constraint: if the rule is spread over two tables, we hit the dependency preservation loss from the 7th post. To keep the copy from drifting, we use the composite foreign key from the 4th post: the (order_id, customer_id) pair will reference the same pair in orders. There is a copy, but the database protects its consistency.
The other decisions are dependency questions too. pre_orders.unit_price is not a copy of books.list_price; it is the price at the moment of the pre-order, a snapshot like order_items.unit_price in the 6th post. Sellable stock is derived from stock and active reservations; stored, it would turn into a synchronization problem from the 8th post, so it will be a view. How many times a coupon was used is derivable too (COUNT(*)), but we will store it deliberately, because it is the most robust way to protect the total usage limit with a constraint.
Physical DDL: Naming, Types and Constraints
Naming follows the Bookstore schema of the SQL Temelleri series: snake_case, plural table names, keys in the form <table>_id, and constraint names with PK_, FK_, UQ_, CK_ and DF_ prefixes. Types follow the decisions of the series: money as DECIMAL(10,2), instants in UTC (Coordinated Universal Time) as DATETIME2(0), calendar days as DATE, statuses as VARCHAR with a CHECK.
ALTER TABLE dbo.books ADD available_from DATE NULL;
GO
UPDATE dbo.books SET available_from = listed_on;
ALTER TABLE dbo.books ALTER COLUMN available_from DATE NOT NULL;
ALTER TABLE dbo.orders ADD CONSTRAINT UQ_orders_order_customer UNIQUE (order_id, customer_id);
GO
CREATE TABLE dbo.pre_orders (
pre_order_id INT IDENTITY(1,1) NOT NULL,
customer_id INT NOT NULL,
book_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL CONSTRAINT DF_pre_orders_status DEFAULT ('Waiting'),
order_id INT NULL,
created_at DATETIME2(0) NOT NULL CONSTRAINT DF_pre_orders_created_at DEFAULT (SYSUTCDATETIME()),
CONSTRAINT PK_pre_orders PRIMARY KEY (pre_order_id),
CONSTRAINT FK_pre_orders_customers FOREIGN KEY (customer_id) REFERENCES dbo.customers (customer_id),
CONSTRAINT FK_pre_orders_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
CONSTRAINT FK_pre_orders_orders FOREIGN KEY (order_id) REFERENCES dbo.orders (order_id),
CONSTRAINT CK_pre_orders_quantity CHECK (quantity > 0),
CONSTRAINT CK_pre_orders_status CHECK (status IN ('Waiting', 'Converted', 'Cancelled')),
CONSTRAINT CK_pre_orders_converted CHECK (
(status = 'Converted' AND order_id IS NOT NULL) OR (status <> 'Converted' AND order_id IS NULL))
);
CREATE UNIQUE INDEX UX_pre_orders_waiting
ON dbo.pre_orders (customer_id, book_id) WHERE status = 'Waiting';
CREATE TABLE dbo.coupons (
coupon_id INT IDENTITY(1,1) NOT NULL,
code VARCHAR(30) NOT NULL,
discount_type VARCHAR(10) NOT NULL,
discount_value DECIMAL(10,2) NOT NULL,
valid_from DATETIME2(0) NOT NULL,
valid_to DATETIME2(0) NOT NULL,
max_redemptions INT NULL,
redemption_count INT NOT NULL CONSTRAINT DF_coupons_redemption_count DEFAULT (0),
CONSTRAINT PK_coupons PRIMARY KEY (coupon_id),
CONSTRAINT UQ_coupons_code UNIQUE (code),
CONSTRAINT CK_coupons_discount CHECK (discount_value > 0
AND (discount_type = 'amount' OR (discount_type = 'percent' AND discount_value <= 100))),
CONSTRAINT CK_coupons_valid_range CHECK (valid_to > valid_from),
CONSTRAINT CK_coupons_redemptions CHECK (redemption_count <= max_redemptions)
);
CREATE TABLE dbo.coupon_redemptions (
order_id INT NOT NULL,
customer_id INT NOT NULL,
coupon_id INT NOT NULL,
discount_amount DECIMAL(10,2) NOT NULL,
redeemed_at DATETIME2(0) NOT NULL CONSTRAINT DF_coupon_redemptions_redeemed_at DEFAULT (SYSUTCDATETIME()),
CONSTRAINT PK_coupon_redemptions PRIMARY KEY (order_id),
CONSTRAINT UQ_coupon_redemptions_coupon_customer UNIQUE (coupon_id, customer_id),
CONSTRAINT FK_coupon_redemptions_coupons FOREIGN KEY (coupon_id) REFERENCES dbo.coupons (coupon_id),
CONSTRAINT FK_coupon_redemptions_orders FOREIGN KEY (order_id, customer_id)
REFERENCES dbo.orders (order_id, customer_id),
CONSTRAINT CK_coupon_redemptions_amount CHECK (discount_amount > 0)
);
CREATE TABLE dbo.stock_reservations (
reservation_id BIGINT IDENTITY(1,1) NOT NULL,
book_id INT NOT NULL,
customer_id INT NOT NULL,
quantity INT NOT NULL,
reserved_at DATETIME2(0) NOT NULL CONSTRAINT DF_stock_reservations_reserved_at DEFAULT (SYSUTCDATETIME()),
expires_at DATETIME2(0) NOT NULL,
CONSTRAINT PK_stock_reservations PRIMARY KEY (reservation_id),
CONSTRAINT FK_stock_reservations_stock_items FOREIGN KEY (book_id) REFERENCES dbo.stock_items (book_id),
CONSTRAINT FK_stock_reservations_customers FOREIGN KEY (customer_id) REFERENCES dbo.customers (customer_id),
CONSTRAINT CK_stock_reservations_quantity CHECK (quantity > 0),
CONSTRAINT CK_stock_reservations_expiry CHECK (expires_at > reserved_at)
);
CREATE INDEX IX_stock_reservations_book_expiry
ON dbo.stock_reservations (book_id, expires_at) INCLUDE (quantity);
GO
CREATE VIEW dbo.available_stock
AS
SELECT s.book_id,
s.quantity_on_hand,
s.quantity_on_hand - ISNULL(r.reserved, 0) AS available_quantity
FROM dbo.stock_items AS s
OUTER APPLY (SELECT SUM(quantity) AS reserved
FROM dbo.stock_reservations
WHERE book_id = s.book_id AND expires_at > SYSUTCDATETIME()) AS r;(77 rows affected)Some decisions were not written in the requirements. available_from was added as nullable, filled and then made NOT NULL: we don't want a "release date unknown" state, and for existing books the listing date is enough. CK_pre_orders_converted writes the rule "a converted pre-order has an order, an unconverted one doesn't"; the answer to the 3rd post's question "what does a nullable foreign key mean?" is a constraint here. CK_coupons_redemptions returns UNKNOWN and passes when max_redemptions is NULL; the behaviour we saw as a trap in the 9th post is used deliberately here to mean "unlimited". Redeeming a coupon is a procedure that writes the counter and the redemption row in the same transaction:
CREATE OR ALTER PROCEDURE dbo.redeem_coupon @order_id INT, @code VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE dbo.coupons
SET redemption_count += 1
WHERE code = @code
AND SYSUTCDATETIME() >= valid_from AND SYSUTCDATETIME() < valid_to;
IF @@ROWCOUNT = 0
THROW 50001, 'Coupon not found or not valid now.', 1;
INSERT INTO dbo.coupon_redemptions (order_id, customer_id, coupon_id, discount_amount)
SELECT o.order_id, o.customer_id, c.coupon_id,
CASE c.discount_type
WHEN 'percent' THEN ROUND(t.total * c.discount_value / 100, 2)
ELSE c.discount_value
END
FROM dbo.orders AS o
CROSS JOIN dbo.coupons AS c
CROSS APPLY (SELECT SUM(quantity * unit_price) AS total
FROM dbo.order_items WHERE order_id = o.order_id) AS t
WHERE o.order_id = @order_id AND c.code = @code;
COMMIT;
END;Now let's test the constraints against the requirement sentences. Every failing row corresponds to a sentence:
INSERT INTO dbo.coupons (code, discount_type, discount_value, valid_from, valid_to, max_redemptions)
VALUES ('SPRING10', 'percent', 10, '2026-01-01', '2027-01-01', NULL),
('ONEUSE50', 'amount', 50, '2026-01-01', '2027-01-01', 1);
EXEC dbo.redeem_coupon @order_id = 1, @code = 'SPRING10';
EXEC dbo.redeem_coupon @order_id = 3, @code = 'SPRING10';
EXEC dbo.redeem_coupon @order_id = 10, @code = 'SPRING10';
GO
EXEC dbo.redeem_coupon @order_id = 2, @code = 'ONEUSE50';
EXEC dbo.redeem_coupon @order_id = 5, @code = 'ONEUSE50';
GO
INSERT INTO dbo.coupon_redemptions (order_id, customer_id, coupon_id, discount_amount)
VALUES (4, 17, 2, 50.00);
GO
SELECT r.order_id, r.customer_id, c.code, r.discount_amount
FROM dbo.coupon_redemptions AS r
JOIN dbo.coupons AS c ON c.coupon_id = r.coupon_id
ORDER BY r.order_id;
SELECT code, max_redemptions, redemption_count FROM dbo.coupons;(2 rows affected)
Msg 2627, Level 14, State 1, Server b8be943222fa, Procedure dbo.redeem_coupon, Line 14
Violation of UNIQUE KEY constraint 'UQ_coupon_redemptions_coupon_customer'. Cannot insert duplicate key in object 'dbo.coupon_redemptions'. The duplicate key value is (1, 3).
Msg 547, Level 16, State 1, Server b8be943222fa, Procedure dbo.redeem_coupon, Line 7
The UPDATE statement conflicted with the CHECK constraint "CK_coupons_redemptions". The conflict occurred in database "dm_12", table "dbo.coupons".
Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_coupon_redemptions_orders". The conflict occurred in database "dm_12", table "dbo.orders".
The statement has been terminated.
order_id customer_id code discount_amount
-------- ----------- -------- ---------------
1 17 SPRING10 104.27
2 28 ONEUSE50 50.00
3 3 SPRING10 154.00
(3 rows affected)
code max_redemptions redemption_count
-------- --------------- ----------------
SPRING10 NULL 2
ONEUSE50 1 1
(2 rows affected)Customer 3's second use of SPRING10 hit the unique constraint, the second use of the single-use coupon hit the counter CHECK, and a redemption row that does not match the order's customer hit the composite foreign key. In the failed procedure calls the counter was rolled back too, because XACT_ABORT is on. The pre-order and reservation rules:
INSERT INTO dbo.pre_orders (customer_id, book_id, quantity, unit_price)
VALUES (3, 41, 1, 200.50);
INSERT INTO dbo.pre_orders (customer_id, book_id, quantity, unit_price)
VALUES (3, 41, 2, 200.50);
GO
UPDATE dbo.pre_orders SET status = 'Converted' WHERE customer_id = 3 AND book_id = 41;(1 rows affected)
Msg 2601, Level 14, State 1, Server b8be943222fa, Line 3
Cannot insert duplicate key row in object 'dbo.pre_orders' with unique index 'UX_pre_orders_waiting'. The duplicate key value is (3, 41).
The statement has been terminated.
Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_pre_orders_converted". The conflict occurred in database "dm_12", table "dbo.pre_orders".
The statement has been terminated.INSERT INTO dbo.stock_reservations (book_id, customer_id, quantity, reserved_at, expires_at)
VALUES (26, 3, 2, SYSUTCDATETIME(), DATEADD(MINUTE, 15, SYSUTCDATETIME())),
(26, 17, 1, SYSUTCDATETIME(), DATEADD(MINUTE, 15, SYSUTCDATETIME())),
(26, 28, 5, DATEADD(MINUTE, -20, SYSUTCDATETIME()), DATEADD(MINUTE, -5, SYSUTCDATETIME()));
SELECT book_id, quantity_on_hand, available_quantity
FROM dbo.available_stock
WHERE book_id = 26;(3 rows affected)
book_id quantity_on_hand available_quantity
------- ---------------- ------------------
26 51 48
(1 rows affected)The second waiting pre-order for the same book hit the filtered unique index, and a pre-order marked "converted" without an order hit the CHECK. There are 51 copies of Dune in stock; two active reservations hold 3, the expired reservation of 5 is not counted, and the sellable stock is 48. Deleting expired rows is the job of a cleanup job; it is not needed for correctness, because the view only looks at active reservations.
Keeping Descriptions in the Schema
Most of the decisions above cannot be read from the column names: what does NULL in max_redemptions mean, who updates redemption_count? This information should be written not on a wiki page but into the schema itself; when the schema changes, the description changes in the same migration. In SQL Server the way to do this is an extended property:
EXEC sys.sp_addextendedproperty
@name = N'MS_Description', @value = N'Maximum number of redemptions. NULL means unlimited.',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'coupons',
@level2type = N'COLUMN', @level2name = N'max_redemptions';
EXEC sys.sp_addextendedproperty
@name = N'MS_Description', @value = N'Denormalized counter kept by dbo.redeem_coupon in the same transaction.',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'coupons',
@level2type = N'COLUMN', @level2name = N'redemption_count';
EXEC sys.sp_addextendedproperty
@name = N'MS_Description', @value = N'NULL until the pre-order is converted into an order.',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'pre_orders',
@level2type = N'COLUMN', @level2name = N'order_id';
SELECT t.name AS table_name, c.name AS column_name, CAST(ep.value AS NVARCHAR(200)) AS description
FROM sys.extended_properties AS ep
JOIN sys.tables AS t ON t.object_id = ep.major_id
JOIN sys.columns AS c ON c.object_id = ep.major_id AND c.column_id = ep.minor_id
WHERE ep.name = N'MS_Description'
ORDER BY t.name, c.column_id;table_name column_name description
---------- ---------------- -----------------------------------------------------------------------
coupons max_redemptions Maximum number of redemptions. NULL means unlimited.
coupons redemption_count Denormalized counter kept by dbo.redeem_coupon in the same transaction.
pre_orders order_id NULL until the pre-order is converted into an order.
(3 rows affected)The name MS_Description is a convention: SSMS (SQL Server Management Studio) and many documentation tools read it. In PostgreSQL the counterpart is COMMENT ON:
CREATE TABLE coupons (
coupon_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code varchar(30) NOT NULL UNIQUE,
max_redemptions integer NULL CHECK (max_redemptions > 0)
);
COMMENT ON TABLE coupons IS 'Discount codes customers can redeem on orders.';
COMMENT ON COLUMN coupons.max_redemptions IS 'Maximum number of redemptions. NULL means unlimited.';
SELECT a.attname AS column_name, col_description(a.attrelid, a.attnum) AS description
FROM pg_attribute AS a
WHERE a.attrelid = 'coupons'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum; column_name | description
-----------------+------------------------------------------------------
coupon_id |
code |
max_redemptions | Maximum number of redemptions. NULL means unlimited.
(3 rows)Which columns need a description? Not every column. The meaning of NULL for every nullable column, the source and update path of every derived or copied column, and a "this is a snapshot" note on every snapshot column are enough. The deliberate normal form departures from the 7th post go into the table description.
Code-First Models in EF Core and Reviewing the Migration
In most .NET projects the schema is not written by hand; it is generated as migrations from the EF Core model. In that case the subject of the review is not the C# classes but the SQL the migration produces. The first model, relying on conventions:
public class Coupon
{
public int CouponId { get; set; }
public string Code { get; set; } = "";
public string DiscountType { get; set; } = "";
public decimal DiscountValue { get; set; }
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
public int? MaxRedemptions { get; set; }
public List<CouponRedemption> Redemptions { get; set; } = [];
}
public class CouponRedemption
{
public int CouponRedemptionId { get; set; }
public int OrderId { get; set; }
public int CustomerId { get; set; }
public int CouponId { get; set; }
public Coupon Coupon { get; set; } = null!;
public decimal DiscountAmount { get; set; }
public DateTime RedeemedAt { get; set; }
}dotnet ef migrations add Initial and dotnet ef migrations script (dotnet-ef 10.0.15, installed through a local tool manifest) produced this SQL:
CREATE TABLE [Coupons] (
[CouponId] int NOT NULL IDENTITY,
[Code] nvarchar(max) NOT NULL,
[DiscountType] nvarchar(max) NOT NULL,
[DiscountValue] decimal(18,2) NOT NULL,
[ValidFrom] datetime2 NOT NULL,
[ValidTo] datetime2 NOT NULL,
[MaxRedemptions] int NULL,
CONSTRAINT [PK_Coupons] PRIMARY KEY ([CouponId])
);
CREATE TABLE [CouponRedemptions] (
[CouponRedemptionId] int NOT NULL IDENTITY,
[OrderId] int NOT NULL,
[CustomerId] int NOT NULL,
[CouponId] int NOT NULL,
[DiscountAmount] decimal(18,2) NOT NULL,
[RedeemedAt] datetime2 NOT NULL,
CONSTRAINT [PK_CouponRedemptions] PRIMARY KEY ([CouponRedemptionId]),
CONSTRAINT [FK_CouponRedemptions_Coupons_CouponId] FOREIGN KEY ([CouponId]) REFERENCES [Coupons] ([CouponId]) ON DELETE CASCADE
);
CREATE INDEX [IX_CouponRedemptions_CouponId] ON [CouponRedemptions] ([CouponId]);The build has no errors and the migration is clean; but the review finds at least six issues. Code is nvarchar(max) and not unique; a max column cannot even be an index key. Money columns get the convention's decimal(18,2), timestamps an unspecified-precision datetime2. DiscountType is free text. The redemption table got a surrogate key, and both rules, "one coupon per order" and "once per customer", are lost. There is an ON DELETE CASCADE that deletes the whole redemption history when a coupon is deleted. And the names don't match the Bookstore's snake_case. Once the rules are written into the model:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Coupon>(e =>
{
e.ToTable("coupons", t =>
{
t.HasCheckConstraint("CK_coupons_discount_type", "discount_type IN ('percent', 'amount')");
t.HasCheckConstraint("CK_coupons_valid_range", "valid_to > valid_from");
});
e.Property(c => c.Code).HasMaxLength(30).IsUnicode(false);
e.HasIndex(c => c.Code).IsUnique();
e.Property(c => c.DiscountType).HasMaxLength(10).IsUnicode(false);
e.Property(c => c.DiscountValue).HasPrecision(10, 2);
e.Property(c => c.ValidFrom).HasPrecision(0);
e.Property(c => c.ValidTo).HasPrecision(0);
});
modelBuilder.Entity<CouponRedemption>(e =>
{
e.ToTable("coupon_redemptions");
e.HasKey(r => r.OrderId);
e.Property(r => r.OrderId).ValueGeneratedNever();
e.HasIndex(r => new { r.CouponId, r.CustomerId }).IsUnique();
e.Property(r => r.DiscountAmount).HasPrecision(10, 2);
e.Property(r => r.RedeemedAt).HasPrecision(0);
e.HasOne(r => r.Coupon).WithMany(c => c.Redemptions)
.HasForeignKey(r => r.CouponId)
.OnDelete(DeleteBehavior.Restrict);
});
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entity.GetProperties())
{
property.SetColumnName(ToSnakeCase(property.Name));
}
}
}
private static string ToSnakeCase(string name) =>
string.Concat(name.Select((ch, i) => i > 0 && char.IsUpper(ch) ? "_" + char.ToLowerInvariant(ch) : char.ToLowerInvariant(ch).ToString()));CREATE TABLE [coupons] (
[coupon_id] int NOT NULL IDENTITY,
[code] varchar(30) NOT NULL,
[discount_type] varchar(10) NOT NULL,
[discount_value] decimal(10,2) NOT NULL,
[valid_from] datetime2(0) NOT NULL,
[valid_to] datetime2(0) NOT NULL,
[max_redemptions] int NULL,
CONSTRAINT [PK_coupons] PRIMARY KEY ([coupon_id]),
CONSTRAINT [CK_coupons_discount_type] CHECK (discount_type IN ('percent', 'amount')),
CONSTRAINT [CK_coupons_valid_range] CHECK (valid_to > valid_from)
);
CREATE TABLE [coupon_redemptions] (
[order_id] int NOT NULL,
[customer_id] int NOT NULL,
[coupon_id] int NOT NULL,
[discount_amount] decimal(10,2) NOT NULL,
[redeemed_at] datetime2(0) NOT NULL,
CONSTRAINT [PK_coupon_redemptions] PRIMARY KEY ([order_id]),
CONSTRAINT [FK_coupon_redemptions_coupons_coupon_id] FOREIGN KEY ([coupon_id]) REFERENCES [coupons] ([coupon_id]) ON DELETE NO ACTION
);
CREATE UNIQUE INDEX [IX_coupon_redemptions_coupon_id_customer_id] ON [coupon_redemptions] ([coupon_id], [customer_id]);
CREATE UNIQUE INDEX [IX_coupons_code] ON [coupons] ([code]);Lengths, precisions, both uniqueness rules, the CHECKs and ON DELETE NO ACTION are now in the migration. Tables and columns are in snake_case; index names, however, stayed in EF Core's IX_ convention, and the decision belongs to the review: either rename them to the Bookstore's UQ_ prefix with HasDatabaseName, or accept and document the difference. The composite foreign key requires the orders entity to be in the model and the (order_id, customer_id) pair to be defined as an alternate key; since orders is outside the model in this example, that step is missing. Migration commands, script and bundle generation are in the "EF Core Migrations" post of the EF Core series, and mapping options such as owned types and value converters in the "EF Core'da Owned Type ve Value Converter" post.
A Schema Review Checklist
The questions to ask when reviewing a schema change (hand-written DDL or a generated migration), one sentence from each post of the series:
- Does every table have a meaningful natural uniqueness, and is it protected with
UNIQUE? Did the business rule get lost next to the surrogate key? - Is the meaning of
NULL(unknown, absent, not yet) written down for every nullable column? IsNOT NULLthe default? - Does the delete behaviour (
CASCADE,NO ACTION, soft delete) come from the model? Is there aCASCADEoutside a weak entity? - Does every column depend on the table's key, the whole key and nothing but the key? If not, is it a deliberate and documented decision?
- Is there a dependency lost in split tables, and what protects it?
- Is the source and update path of every copied or derived column known? Are snapshots separated from copies?
- Is money
DECIMAL, are instants UTC with fixed precision, are calendar daysDATE? Have the length and Unicode needs of text columns been considered? - Is there a comma-separated list, a numbered column, EAV (Entity-Attribute-Value), a one big lookup table or an unconstrained JSON (JavaScript Object Notation) column?
- Does the reporting need strain the OLTP (Online Transaction Processing) schema, or is a separate dimensional model needed?
- Do the names follow the existing schema's convention? Can the change be deployed in a backward-compatible way?
In Production
Write constraints like requirement tests. t1.sql and t2a.sql above try one failing row for every business rule. If these scripts run against a test database in CI (Continuous Integration), someone removing a constraint "temporarily" gets noticed.
Don't try to put time rules into constraints. The rule "pre-orders are only taken for books whose release date is in the future" cannot be written as a CHECK with SYSUTCDATETIME(), because the rule turns a row that is correct today into a wrong one tomorrow. This rule is checked in application code at write time; the database protects only rules that are independent of time.
Add new columns in a backward-compatible way. available_from was first added as nullable, filled, then made NOT NULL; on a large table these steps happen in separate deploys with a chunked backfill. The expand-contract flow is in the "Migration'ların Deploy'daki Yeri" post of the Deployment Stratejileri series.
Watch hot rows such as counters and reservations. The redemption_count row of a popular coupon means heavy writes to the same row at the moment of a campaign. If that becomes a problem at scale, the options from the 8th post (batch, event-based updates) or designs that split the coupon into pieces come to the table.
Common Pitfalls
Turning a requirement sentence into a single table and skipping the rules. The sentence "once per customer" is not a table but a UNIQUE constraint; "at most one coupon per order" is the choice of primary key. The rules hide inside the sentences.
Applying the migration an ORM (Object-Relational Mapper) generates without reading it. Conventions produce a working schema, not a correct one; the ON DELETE CASCADE in the first migration above deletes financial history when a coupon is deleted.
Storing a derivable value everywhere, or nowhere. Sellable stock was derived, the coupon counter was stored; both are deliberate decisions and their reasons are written in the schema. There is no single rule; every value gets its own decision.
Keeping descriptions outside the schema. A wiki page is not updated together with the schema; an extended property or COMMENT ON is updated in the same migration.
A Small Experiment
★☆☆ Add the rule "a coupon applies only to books in a given category" to the coupons table. Is it a column or a new table? What changes if a coupon can apply to several categories?
★★☆ Write a procedure named dbo.convert_pre_orders that converts the waiting pre-orders of released books into a single order per customer. In which order does CK_pre_orders_converted tell you to write?
★★★ Add the Order entity to the EF Core model and model the (order_id, customer_id) composite foreign key of coupon_redemptions with HasAlternateKey and HasForeignKey. Compare the generated migration with the T-SQL DDL in this post: which differences remain, and which would you accept?
The series ends here: starting from a flat order spreadsheet, we went through the ER diagram, keys, functional dependencies, normal forms, denormalization, anti-patterns, dimensional and document models, and finally reached a schema that can be reviewed. The practical problems built on top of this schema (public ids, soft delete, audit, multilingual data, multi-tenancy) are in the Çözüm Desenleri series; the engine side of the schema is in the SQL Server Derinlemesine series, and what happens when it grows is in the Veritabanı Performansı ve Scaling series.