The first CREATE TABLE is easy. The third relationship reveals what the first table meant. Modeling data on paper exposes that question before rows and code depend on the answer.
Start Modeling Data With Business Nouns and Events
Write down the entities the system needs to remember: customer, order, product, payment. Then describe events that connect them. A noun alone is not enough. An order can have several lines, and a payment can cover more than one order under some business rules.
I ask what one row represents in each proposed table. One row per order and one row per order line are distinct grains. A vague table called OrderData tends to mix them and makes totals unreliable later.
Use real questions from the application to test the sketch. Can it show a customer’s open orders? Can it show the product price used on an old order? These questions reveal which facts need history.
SELECT CustomerId, COUNT_BIG(*) AS OrdersPlaced
FROM dbo.Orders
GROUP BY CustomerId;Draw Relationships and Cardinality
Mark whether each relationship is one-to-one, one-to-many, or many-to-many. A customer can have many orders. An order can have many lines. A product can appear on many lines. A many-to-many relationship needs a linking table with its own keys and rules.
I look for optional relationships. Can an order exist before a customer is verified? Can a payment exist before allocation? NULL foreign keys can be legitimate, but they should represent a named state rather than an unfinished sketch.
A diagram should show ownership of deletion and retention. If a customer is retired, old orders can still be required. That affects foreign keys and application behavior. Decide before writing cascading deletes.
SELECT fk.name, OBJECT_NAME(fk.parent_object_id) AS child_table,
OBJECT_NAME(fk.referenced_object_id) AS parent_table
FROM sys.foreign_keys AS fk
ORDER BY child_table, parent_table;Choose Keys for Different Jobs
A natural key comes from the business domain, such as an account code. A surrogate key is an internal identifier. Use each where it helps. A surrogate primary key can make joins stable while a unique constraint on the natural key preserves business identity.
I ask whether the natural key can change or be reused. A customer email is a weak identity key if customers change addresses or share a mailbox. Keep source identifiers for reconciliation even when the table uses a surrogate key.
Composite keys can express tenant-scoped identity. A code unique within one tenant needs TenantId in its uniqueness rule. The data model should reflect that scope. Global uniqueness added by accident can reject valid customers.
CREATE UNIQUE INDEX UX_Customer_Tenant_Code
ON dbo.Customer(TenantId, CustomerCode);
Put Facts at One Grain When Modeling Data
Do not store order header amount on every order line and then sum it as if it were a line fact. Keep header facts at order grain and line facts at line grain. A reporting model can combine them through keys, but each table should have a clear row meaning.
I write a one-sentence grain declaration for every fact table. That sentence makes later GROUP BY and JOIN reviews much easier. It also exposes whether a table is trying to hold several event types in one nullable structure.
History needs a separate rule. A product’s current price and the price charged on a past order are different facts. Store the charged price on the order line if that is the business record. A join to the current product price cannot recreate it.
SELECT o.OrderId, SUM(l.Quantity * l.UnitPrice) AS LineTotal
FROM dbo.Orders AS o
JOIN dbo.OrderLine AS l ON l.OrderId = o.OrderId
GROUP BY o.OrderId;Add Constraints to Match the Sketch
Primary keys, unique constraints, foreign keys, CHECK constraints, and NOT NULL rules turn the sketch into enforceable structure. They protect data written by every application path. A diagram without constraints is a suggestion, not a database contract.
I keep constraints simple and named. A nonnegative quantity rule belongs near the column. A changing approval policy can need a lookup or workflow table. Do not force every business process into one CHECK expression.
Test negative cases. Try a duplicate natural key, an orphaned child, and an invalid amount in a safe test database. The error path should be understandable. A model is useful when it prevents bad states, not only when it draws them.
ALTER TABLE dbo.OrderLine
ADD CONSTRAINT CK_OrderLine_Quantity_Positive
CHECK (Quantity > 0);Review Change and Retention While Modeling Data
Ask which values can change, who changes them, and whether old values must remain visible. A customer address can change while an old shipment needs its original destination. A current column alone cannot answer both questions.
When modeling data, I settle retention before adding cascade behavior. Legal and operational needs can require old transactions after a customer account is closed. A soft-delete flag can help, but it does not replace a clear retention policy and access rule.
Consider effective dates when a relationship or classification changes over time. They bring complexity, so use them where historical questions demand it. A simple current-state table is easier when history is not required.
Test the Model With Real Queries
Write a few representative SELECT statements from the sketch before building every table. If a basic question requires awkward repeated joins or cannot be answered without guessing a grain, revisit the model. Sample inserts and constraints can reveal missing optionality.
I review the model with the people who know the domain. A developer can choose keys and types, but a business owner knows whether one payment can cover several orders. The best time to learn that is before the migration script exists.
Modeling data is a short investment in fewer surprises. Draw entities, cardinality, keys, and history rules, then test them with questions and negative cases. The first CREATE TABLE becomes easier once its row meaning is settled.
Before creating a table, write down the grain of one row. Is it one order, one order line, or one status change? That sentence guides the key, foreign keys, and uniqueness rules. I ask for sample questions the system must answer, then check whether the proposed model supports them without ambiguous joins. A model that cannot explain its row grain will confuse every later report.
Names and data types should reflect the business meaning. A date of birth is a date, while an event instant needs a time and zone policy. Decide whether a missing value means unknown, not applicable, or not yet collected. Which relationships are mandatory? Put that rule into constraints where possible. The first model is a hypothesis, so test it with realistic inserts, updates, deletions, and a few hard queries before calling it finished.
Related reading on this blog: Find Untrusted Foreign Key and Rules of Third Normal Form and Normalization Advantage: 3NF.
A data model is not a picture for a meeting, it is a set of promises the tables must keep.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
