Relational Database Design
Relational database design turns business facts into related tables, keys, and rules. It helps you store each fact in a clear place and prevent invalid or contradictory relationships.
itDatabases and data storage | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Intro
Relational Database Design
Relational database design defines the structure that makes stored facts usable and trustworthy. You decide what one row means, how rows identify each other, and which states the database must reject.
A relational database stores data in tables. The important part is not the grid. It is the contract behind the grid: each table represents one kind of fact, each row represents one instance of that fact, and relationships express how facts connect.
Start from the domain, not from columns. An ordering system may contain customers, orders, products, and order lines. Ask what each term means, which facts change independently, and which rules must remain true after every write.
Design from facts and rules
Write the grain of every table as one sentence. For example: “One row in order_line records one product on one order.” Grain prevents a familiar error: mixing order-level facts, such as the order date, with line-level facts, such as quantity.
Then list the business rules. An order belongs to one customer. An order has one or more lines. A line names one product. A product code is unique. A quantity is positive. These statements become relationships, keys, and constraints.
A diagram helps people review the model. It does not enforce the model. The database needs declarations that reject invalid rows and invalid references.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://research.ibm.com/publications/a-relational-model-of-data-for-large-shared-data-banks
Supports
- The relational model is based on n-ary relations and addresses data representation independence
- Normalization and redundancy-consistency problems are core relational-model concerns
- https://www.postgresql.org/docs/current/ddl-constraints.html
Supports
- NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints have distinct roles
- A primary key is unique and not null
- Foreign keys preserve referential integrity by requiring matches in a referenced table
- Composite foreign keys and unique constraints can cover multiple columns
- CHECK constraints are row-oriented and should not be used for arbitrary cross-row guarantees
- Foreign-key actions are part of a declared relationship contract
- https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
Supports
- Normalization reduces redundancy and inconsistent dependencies
- First normal form removes repeating groups and uses primary keys
- Second normal form separates facts that do not depend on the complete key
- Third normal form removes fields that do not depend on the key
- Normalization can require additional tables and needs practical judgment
- https://www.postgresql.org/docs/current/indexes.html
Supports
- Indexes can accelerate row retrieval
- Indexes add system overhead and should be used deliberately
- https://dev.mysql.com/doc/workbench/en/wb-design-modeling-interface.html
Supports
- MySQL Workbench provides model editing, EER diagrams, table creation, and foreign-key relationship creation
- Existing schemas can be reverse engineered into a model
- https://github.com/mgramin/awesome-db-tools
Supports
- The curated list includes dbdiagram.io under database-design tools
- The curated list includes pgModeler under database-design tools
- The curated list includes Atlas under schema migration tools