What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A reliable relational schema starts with the facts your application must store, the relationships between those facts, and the rules that must always hold. Model those explicitly with well-chosen keys, constraints, and data types; then check the design against real queries and the exact database engine and version you will run.
Start with the domain, not the screens
List the things the application needs to remember, the facts about each thing, and how those things relate. A customer, an order, and a product are possible entities; a customer’s email address or an order’s placement time are attributes. A screen is not automatically a table: several screens may use the same entity, while one screen may show data drawn from several related tables.
As an Amazon Associate I earn from qualifying purchases.
Write down rules alongside the model: which values are required, which must be unique, which states are allowed, and whether a record may exist without a related record. These rules help distinguish entities from attributes and expose relationships before they become scattered application assumptions. Microsoft’s database design basics guidance describes organizing information into related tables rather than storing repeated details together.
Represent relationship cardinality
- One-to-many: If one customer can place many orders and each order belongs to one customer, store the customer reference on the order.
- Many-to-many: If a product can appear in many orders and an order can contain many products, represent the relationship with a bridge table such as order items. That table can also hold relationship-specific facts, such as quantity or the price recorded for that order.
- Repeating values: Do not pack independently repeating items into one field, such as a comma-separated list of phone numbers. Represent them as rows in a related table when the application needs to validate, find, or update them independently.
Choose a clear identity for every table
A primary key identifies each row and must be unique and non-null. Choose an identity that remains stable for the represented entity or relationship. A customer’s changeable email address, for example, is usually a poor identity if the business permits it to change; it may instead be protected with a separate uniqueness rule if the domain requires that.
#1 Best Overall
For a bridge table, a composite key can express that a particular pair of foreign-key values may occur only once. Alternatively, a separate row identifier may be useful when other tables need to reference a relationship row independently. The choice should match the domain and the way rows will be referenced, not simply a preference for short keys.
Primary-key implementation details are engine-specific. SQL Server documentation says a primary key creates a unique index and its columns are non-null; PostgreSQL 18 likewise documents a primary key as creating a unique B-tree index and forcing its columns to NOT NULL. See Microsoft’s SQL Server primary and foreign key constraints and PostgreSQL 18’s constraints documentation.
Use constraints to protect relationships and rules
A foreign key makes the database reject a reference to a row that does not exist in the referenced table. It is a practical safeguard when data can be written by more than one application path, or when application code may contain defects. PostgreSQL 18 describes a foreign key as requiring values in one column or group of columns to match values in a row of another table.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesDecide what should happen when referenced data changes or is deleted. Restricting a deletion may be appropriate when dependent records must be retained; cascading may be appropriate when dependent rows have no independent meaning and should be removed with their parent. Select the action from the business rule, and verify the chosen engine’s behavior rather than relying on assumptions.
Declare the rules the database can enforce
NOT NULL: Require a value when a missing value would make the row invalid.UNIQUE: Prevent duplicate values or combinations where the domain requires uniqueness.CHECK: Restrict a value to an allowed range or set of conditions where supported and appropriate.- Defaults: Supply a value only when a defined default is genuinely part of the domain; do not use a default to conceal missing information.
- Foreign keys: Require related rows to exist and define deliberate update or delete behavior.
Choose types that reflect the meaning of the data. A phone number is not a quantity to calculate with; a timestamp is not just arbitrary text; and money needs a representation appropriate to the application’s currency and precision rules. Model status values and nullability intentionally, then check the selected engine’s DDL rules. The MySQL 8.4 CREATE TABLE documentation and PostgreSQL’s constraints documentation describe their respective syntax and behavior.
Normalize facts to prevent avoidable duplication
Normalization is a way to organize related facts so the same fact does not need to be maintained in multiple places. For example, if every product row repeats its category description, changing that description requires updating many product rows. A separate categories table, referenced by products, gives the category description one home and lets the database relate products to it. Microsoft’s database design guidance uses this kind of split to illustrate relational design.
Rank #3
Do not treat normalization as a contest to maximize the number of tables. Separate facts when they have their own identity, are repeated, or need independent rules and updates. Then ensure the design still supports the application’s actual reads and writes. If a workload later justifies a deliberate denormalization, treat it as a trade-off: identify which data is duplicated, how it stays consistent, and what benefit the measured workload gains.
Add indexes for actual access patterns
A primary key commonly has a unique index, but a foreign key does not guarantee that the referencing columns have an index. SQL Server explicitly says it does not automatically create an index for a foreign key; it notes that such an index is often useful when those columns are used in joins or checks. Check the behavior of the engine you use rather than assuming another product behaves the same way.
Consider additional indexes for important filters, joins, ordering, and uniqueness requirements. An index can make some reads more efficient, but indexes also consume storage and add maintenance work to writes. Indexing every column is not a sound default. Microsoft’s SQL Server index design guide covers design considerations; the right choices still depend on your database and workload.
Use queries and plans to decide
- Identify representative queries the application must run, including common filters, joins, and sort orders.
- Load representative data and run those queries on the target database engine and version.
- Inspect the engine’s query plans to see how the database is accessing the data.
- Add or adjust indexes only where the workload and plans provide a reason, then test the same queries and relevant writes again.
There is no universal benchmark threshold or index recipe established for every schema. A useful index depends on the data distribution, query shape, write workload, and database engine.
Test the schema before relying on it
Run realistic inserts, updates, deletes, and reads against the target engine. Test valid cases as well as invalid ones: duplicate values that should be rejected, missing required values, references to nonexistent rows, disallowed states, and deletion or update behavior for related records. Confirm that the database rejects violations rather than relying only on an application form to prevent them.
Review query plans for representative application queries and repeat the checks after schema or index changes. PostgreSQL, MySQL, and SQL Server share relational concepts, but their DDL syntax and details differ. Use the documentation for the exact product and version that will run the application, and test migrations and compatibility in that environment.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
