5 entitiesorder historymany-to-many
1. E-commerce orders
A customer creates orders, an order contains products, and payments are recorded separately from the order itself. The important distinction is between a product's current catalogue facts and the historical facts that made up a purchase.
user1 ── Norder1 ── Npayment
·
order1 ── Norder_productN ── 1product
What the relationships express
order.user_id is a non-identifying foreign key: it says who placed an order without making the user id part of the order's identity.order_product resolves the many-to-many relationship. Quantity belongs on that row because it is a fact about this product in this order.- In the sample,
payment.order_id is both a key and a foreign key. It makes the order reference part of the payment record's identity.
Questions before implementation
- Decide whether failed attempts, retries, and partial refunds are states of one payment or their own history records.
- A receipt must not change when a product is renamed or repriced. Consider snapshots of the sold name and unit price on an order item.
- If fulfillment can split, add
shipment and shipment_item rather than treating an order as one shipment forever.
Choose a relationship for the rule it protects, not because two objects appear together on one screen. For example, a product with order history may need to be deactivated rather than deleted.
Open the e-commerce sample
5 entitiesauthors and commentstag bridge
2. Blog publishing
A blog is a compact way to separate facts with different lifecycles: an author, a post, a comment, and a tag. It also shows why tags should not be stored as a comma-separated field on a post.
User1 ── NPost1 ── NComment
·
Post1 ── NPostTagN ── 1Tag
What the relationships express
Post.user_id gives one author many posts while keeping title, content, and publication time independent of the profile.- A comment refers to both its post and its author. Decide what should remain if either the post is removed or the user leaves.
PostTag connects many posts to many tags. In a production schema, a UNIQUE constraint on (post_id, tag_id) prevents duplicate tagging.
When requirements grow
- For replies, add an optional
Comment.parent_comment_id; define nesting depth and the treatment of a removed parent comment too.
- If moderation happens before publication, decide whether
published_at is enough or a status and transition history are needed.
- If posts survive account deletion, use anonymisation or deactivation rather than assuming every user row can disappear.
A bridge table is especially useful when the relationship gains facts of its own. A tagged-at time, an editor, or a display order all have an obvious home on PostTag.
Open the blog sample
2 entitiesdepartment assignmentself-reference
3. HR and reporting lines
This model demonstrates a table that refers to itself. An employee belongs to a department and may, but does not have to, point to another employee as a manager.
department1 ── Nemployee
·
employee0..1 ── Nemployee(manager)
What the relationships express
employee.dept_id allows a department to have many employees while a newly created department may have none yet.employee.parent_emp_id optionally points back to employee.emp_id. A top-level leader has no manager, so NULL is meaningful.- A reporting line and a job title are separate ideas. Whether
position becomes a reference table depends on the organisation's HR rules.
Operational rules to add
- Prevent an employee from managing themselves or two employees from forming a management cycle, in application logic or database checks.
- If transfers matter historically, keep
employee_department_history instead of overwriting only the current dept_id.
- For matrix management, replace the single self-reference with an employee-manager relationship table.
Self-references keep a hierarchy compact, but they require rules for cycles, deletion, and recursive queries. Write the business statement — for example, “can an employee have no manager?” — before deciding whether the foreign key is nullable.
Open the HR sample