Modeling Relationships: One-to-One, One-to-Many, Many-to-Many, Self-Reference and Weak Entities

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10).

Guess First: Adding a Column to a Link Table

In the Bookstore, the M-N relationship between books and authors is built, as in most projects, with a table made of two foreign keys. The primary key is the combination of the two columns, and the author order lives in the table too:

CREATE TABLE dbo.book_authors (
    book_id      INT     NOT NULL,
    author_id    INT     NOT NULL,
    author_order TINYINT NOT NULL,
    CONSTRAINT PK_book_authors PRIMARY KEY (book_id, author_id),
    CONSTRAINT FK_book_authors_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT FK_book_authors_authors FOREIGN KEY (author_id) REFERENCES dbo.authors (author_id),
    CONSTRAINT UQ_book_authors_order UNIQUE (book_id, author_order)
);
INSERT INTO dbo.book_authors (book_id, author_id, author_order)
VALUES (26, 18, 1), (42, 29, 1), (42, 30, 2), (41, 20, 1);

A new requirement arrives: a book's credits will show translators, editors and foreword writers next to the authors, and the same person can appear on a book in more than one role. Say that in a new edition of Good Omens, Neil Gaiman also wrote the foreword. The team adds a role column to the table and writes the new row. Guess: which of these two commands fails?

ALTER TABLE dbo.book_authors
    ADD contribution VARCHAR(20) NOT NULL
        CONSTRAINT DF_book_authors_contribution DEFAULT ('author') WITH VALUES;
GO

INSERT INTO dbo.book_authors (book_id, author_id, author_order, contribution)
VALUES (42, 30, 3, 'foreword');

Msg 2627, Level 14, State 1, Server b8be943222fa, Line 2
Violation of PRIMARY KEY constraint 'PK_book_authors'. Cannot insert duplicate key in object 'dbo.book_authors'. The duplicate key value is (42, 30).
The statement has been terminated.

The column was added without trouble, and the existing rows got the value 'author'. But the new row did not go in, because the primary key still says "a person appears once on a book". Adding a role actually changed the identity of the relationship: what tells one row apart is no longer the book and person pair but the book, person and role trio. When you see the link table as "a technical detail made of two foreign keys", you never make this decision. This post is about taking relationships down to tables: 1-N, M-N, 1-1, self-reference, ternary relationships, weak entities, and where and how each of them lands in the schema. The details of CREATE TABLE and constraint syntax are in the 4th post of the SQL Temelleri series; the focus here is how the decision comes out of the model.

1-N: Why Is the Foreign Key Always on the "Many" Side?

A customer has many orders, an order has one customer. The only correct way to build this relationship is to put a customer_id column on the table on the "many" side, that is, orders. Doing the opposite, writing order numbers into the customers table, means either squeezing a list into one column or opening repeating columns such as order_1, order_2, order_3. The order spreadsheet, the working material of this series, with the books written into a single cell separated by commas, is exactly an example of this mistake.

There is a rule behind it: in the relational model every cell holds a single value. The "one" side cannot list the "many", but each row of the "many" can point to a single "one". The arrow is always drawn from many to one. Optionality decides whether the foreign key column can be NULL: if an order's customer is mandatory, orders.customer_id NOT NULL. How this relationship is mapped in Entity Framework Core (EF Core) is in the "EF Core'da İlişkiler 1: One-to-Many, Foreign Key ve Cascade Delete" post of the EF Core series.

M-N: An Associative Entity Is Not Just a Link Table

You cannot build an M-N relationship by putting a foreign key on one of the two tables; both sides are "many". The solution is a third table: an associative entity whose every row represents "a single link between this book and this author". What matters is accepting that this table has a meaning of its own. book_authors is a concept: a person's contribution to a book. The contribution has a position, a kind, and may later have other attributes (such as a royalty rate). To fix the error above, we have to rebuild the primary key around the new identity:

ALTER TABLE dbo.book_authors DROP CONSTRAINT PK_book_authors;
ALTER TABLE dbo.book_authors
    ADD CONSTRAINT PK_book_authors PRIMARY KEY (book_id, author_id, contribution);
GO

INSERT INTO dbo.book_authors (book_id, author_id, author_order, contribution)
VALUES (42, 30, 3, 'foreword');

SELECT b.title, a.full_name, ba.contribution, ba.author_order
FROM dbo.book_authors AS ba
JOIN dbo.books AS b ON b.book_id = ba.book_id
JOIN dbo.authors AS a ON a.author_id = ba.author_id
WHERE ba.book_id = 42
ORDER BY ba.author_order;

(1 rows affected)
title       full_name        contribution  author_order
----------  ---------------  ------------  ------------
Good Omens  Terry Pratchett  author        1
Good Omens  Neil Gaiman      author        2
Good Omens  Neil Gaiman      foreword      3

(3 rows affected)

On a live system this two-line change is not so innocent. The primary key is usually the clustered index; dropping and rebuilding it means rewriting the table, making everyone who accesses it wait, and, if other foreign keys point to this table, dropping and rebuilding those too. Modeling the associative entity as "a concept" from the start makes you ask on day one: What is the identity of this relationship? Can the same person appear twice on the same book? If the answer is "yes, with different roles", the role is part of the key from the beginning.

The UQ_book_authors_order constraint in the table is a modeling decision too: two contributions on the same book cannot have the same position. Leaving this to application code means that on the day two screens save at the same time, you see two "first authors" on the cover. How to map an associative entity as its own class in EF Core is in the "EF Core'da İlişkiler 2: One-to-One, Many-to-Many ve UsingEntity" post of the EF Core series.

The Three Real Reasons for 1-1

A real 1-1 relationship suggests that the two tables are actually a single entity, and most of the time they are. The legitimate reasons for a separate table are limited. The first is optional and large data. A book's long description and table of contents do not exist for every book, and when they do, they are read only on the detail page. The query that fetches the category list does not need to deal with rows carrying these texts. The second is a security split. A customer's password hash or authentication data can live in a table that not everyone who reads the customer profile should be allowed to read; granting permissions at table level leaves less room for error than at column level. The third is subtypes. A book can be an e-book or printed; the file size and format specific to e-books and the weight and page size specific to printed books can live in separate tables. How EF Core maps this structure as TPT (Table per Type) is in the "EF Core'da Inheritance Mapping" post of the EF Core series.

The foreign key goes on the optional side and is the primary key at the same time:

CREATE TABLE dbo.book_details (
    book_id           INT            NOT NULL,
    long_description  NVARCHAR(MAX)  NOT NULL,
    table_of_contents NVARCHAR(MAX)  NULL,
    CONSTRAINT PK_book_details PRIMARY KEY (book_id),
    CONSTRAINT FK_book_details_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id)
);
INSERT INTO dbo.book_details (book_id, long_description)
VALUES (26, N'Set on the desert planet Arrakis...');
INSERT INTO dbo.book_details (book_id, long_description)
VALUES (26, N'A second description for the same book');

(1 rows affected)
Msg 2627, Level 14, State 1, Server b8be943222fa, Line 10
Violation of PRIMARY KEY constraint 'PK_book_details'. Cannot insert duplicate key in object 'dbo.book_details'. The duplicate key value is (26).
The statement has been terminated.

Because book_details.book_id is both the primary key and the foreign key, a book can have at most one detail row; the schema guarantees the 1-1 as the relationship's maximum. Had you put the foreign key on the other side, as books.book_details_id, you would carry a nullable column for every book without details and write an extra unique constraint to stop two books pointing at the same detail row. The rule is simple: the foreign key sits on the side that needs the other in order to exist.

Self-Reference: A Table That Points to Itself

Categories are nested: the parent of Science Fiction is Fiction. This unary relationship is built with a foreign key that points at the table's own primary key: categories.parent_category_id. The top-level categories have no parent, so the column is nullable. The foreign key blocks a parent that does not exist, but it does not protect the meaning of the hierarchy:

ALTER TABLE dbo.categories
    ADD CONSTRAINT CK_categories_not_own_parent CHECK (parent_category_id <> category_id);

UPDATE dbo.categories SET parent_category_id = 2 WHERE category_id = 2;

UPDATE dbo.categories SET parent_category_id = 3 WHERE category_id = 1;
SELECT category_id, name, parent_category_id FROM dbo.categories;

Msg 547, Level 16, State 1, Server b8be943222fa, Line 4
The UPDATE statement conflicted with the CHECK constraint "CK_categories_not_own_parent". The conflict occurred in database "dm_03", table "dbo.categories".
The statement has been terminated.

(1 rows affected)
category_id  name             parent_category_id
-----------  ---------------  ------------------
1            Fiction          3
2            Science Fiction  1
3            Fantasy          1

(3 rows affected)

The CHECK constraint stopped a category from being its own parent. But with the second UPDATE I made Fantasy the parent of Fiction, and Fiction is already the parent of Fantasy. Now there is a cycle, and every recursive CTE (Common Table Expression) that walks this rootless tree will either hit the MAXRECURSION limit or return wrong results. A CHECK constraint looks at a single row; a cycle is a situation that several rows create together. Preventing cycles takes a trigger, application code that does all writes in one place, or a different hierarchy model. The comparison of adjacency lists, closure tables and hierarchyid is in the "SQL'de Hiyerarşik Veri" post of the Çözüm Desenleri series; the recursive query itself is in the "CTE, View ve Recursive Sorgular" post of the SQL Temelleri series.

Why Can't a Ternary Relationship Be Split into Two Binary Ones?

The bookshop starts working with foreign publishers, and there is a rule: the printing rights of a book in a country belong to a single publisher. "This publisher may print this book in this country" is a single fact connecting three entities at once. The primary key is (book_id, country_code), because by the rule a book and a country together determine one publisher. The publisher names are made up for the example:

CREATE TABLE dbo.publishers (
    publisher_id INT           NOT NULL,
    name         NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_publishers PRIMARY KEY (publisher_id)
);
CREATE TABLE dbo.publishing_rights (
    book_id      INT     NOT NULL,
    publisher_id INT     NOT NULL,
    country_code CHAR(2) NOT NULL,
    CONSTRAINT PK_publishing_rights PRIMARY KEY (book_id, country_code),
    CONSTRAINT FK_publishing_rights_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT FK_publishing_rights_publishers FOREIGN KEY (publisher_id) REFERENCES dbo.publishers (publisher_id)
);
INSERT INTO dbo.publishers VALUES (1, N'Northwind Books'), (2, N'Harbor Press');
INSERT INTO dbo.publishing_rights VALUES (26, 1, 'TR'), (26, 2, 'DE'), (42, 1, 'TR');

Dune's rights in Turkey are with Northwind Books, in Germany with Harbor Press. This table may look "needlessly complex", and someone may suggest splitting it into two binary relationships: book–publisher and book–country. Let's split it, join it back and look for rows that are not in the original table:

SELECT DISTINCT book_id, publisher_id INTO dbo.book_publishers FROM dbo.publishing_rights;
SELECT DISTINCT book_id, country_code INTO dbo.book_countries FROM dbo.publishing_rights;

SELECT bp.book_id, bp.publisher_id, bc.country_code
FROM dbo.book_publishers AS bp
JOIN dbo.book_countries AS bc ON bc.book_id = bp.book_id
EXCEPT
SELECT book_id, publisher_id, country_code
FROM dbo.publishing_rights;

(3 rows affected)

(3 rows affected)
book_id  publisher_id  country_code
-------  ------------  ------------
26       1             DE
26       2             TR

(2 rows affected)

Two spurious rows came out. The joined data says "Northwind Books may print Dune in Germany" and "Harbor Press may print Dune in Turkey"; both are wrong. The two split tables answer "Which publishers work with Dune?" and "In which countries is Dune sold?" correctly, but the information "which publisher in which country?" is gone, because it belongs neither to the book–publisher pair nor to the book–country pair. It belongs to the three together. If splitting a table and joining it back produces spurious rows, the split is lossy. Under which conditions a ternary relationship really can be split into three parts is the topic of join dependencies and 5NF, covered in detail in the 7th post of the series.

Weak Entities and Deriving ON DELETE from the Model

An order item cannot exist on its own: you cannot talk about an item without saying "item 1 of order 1003". In the ER (Entity-Relationship) model this is called a weak entity, and its relationship with its parent an identifying relationship. A weak entity's key contains the parent's key:

CREATE TABLE dbo.orders (
    order_id   INT          NOT NULL,
    ordered_at DATETIME2(0) NOT NULL,
    CONSTRAINT PK_orders PRIMARY KEY (order_id)
);
CREATE TABLE dbo.order_items (
    order_id   INT           NOT NULL,
    line_no    SMALLINT      NOT NULL,
    book_id    INT           NOT NULL,
    quantity   INT           NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT PK_order_items PRIMARY KEY (order_id, line_no),
    CONSTRAINT FK_order_items_orders FOREIGN KEY (order_id)
        REFERENCES dbo.orders (order_id) ON DELETE CASCADE,
    CONSTRAINT FK_order_items_books FOREIGN KEY (book_id)
        REFERENCES dbo.books (book_id)
);
INSERT INTO dbo.orders VALUES (1001, '2026-03-02T09:30:00'), (1003, '2026-03-09T10:15:00');
INSERT INTO dbo.order_items VALUES
    (1001, 1, 26, 1, 390.50), (1001, 2, 42, 1, 340.90),
    (1003, 1, 26, 2, 351.45);

The two foreign keys have different delete behaviours, and both come from the model. Because an item is meaningless without its order, deleting the items when the order is deleted is consistent with the model: ON DELETE CASCADE. The book, on the other hand, is not the item's parent but an independent entity the item refers to. Deleting a book that has been sold breaks the meaning of past orders; so the default NO ACTION is the right choice. Let's try:

DELETE FROM dbo.orders WHERE order_id = 1001;
SELECT order_id, line_no, book_id FROM dbo.order_items;

DELETE FROM dbo.books WHERE book_id = 26;

(1 rows affected)
order_id  line_no  book_id
--------  -------  -------
1003      1        26

(1 rows affected)
Msg 547, Level 16, State 1, Server b8be943222fa, Line 4
The DELETE statement conflicted with the REFERENCE constraint "FK_book_authors_books". The conflict occurred in database "dm_03", table "dbo.book_authors", column 'book_id'.
The statement has been terminated.

When 1001 was deleted both of its items went with it, and the item of 1003 stays. The attempt to delete Dune was rejected. The error came from book_authors, because in this demo database three tables depend on Dune (book_authors, book_details, order_items) and SQL Server reports the first conflicting constraint. Whichever it is, the result is the same: a book cannot be deleted while rows depend on it.

There is one more subtlety. According to the requirement list in the first post of the series, orders are not deleted anyway, they are cancelled. Then why CASCADE? Because a foreign key's delete behaviour answers "What should happen when someone deletes this row, whoever it is?" In exceptional cases such as cleaning up test data, a request to erase personal data or a draft order created by mistake, items must not be left without an owner. Some teams prefer the opposite: leaving NO ACTION on the order as well and making deletion a deliberate two-step operation. Both are defensible; what is not defensible is leaving the decision to the default. The "Function'lar ve Trigger'lar" post of the SQL Server Derinlemesine series explains how cascade chains and triggers behave together.

Nullable Foreign Keys: Unknown or None?

In the Bookstore schema books.category_id is nullable, and two books have no category. Think also of an orders.coupon_id column that will come later. Both columns hold NULL, but they do not say the same thing. For an order without a coupon, NULL means "no coupon", which is known, definite information. For a book without a category, NULL most likely means "not classified yet", that is, unknown. If someone writing a report reads both the same way, they produce a line called "revenue of uncategorized books", and the reader thinks it is a category.

You should write this difference into the model explicitly. There are three ways. The first is to document the column's meaning: an extended property in SQL Server, COMMENT ON COLUMN in PostgreSQL; in the last post of the series we build the schema together with these descriptions. The second is to express the "unknown" state with a separate value: a real category row called "Uncategorized". Sometimes that is right, sometimes it is a sentinel value trap; the discussion is in the "Sentinel Value'lar" post of the Çözüm Desenleri series. The third is to question the optionality again: if the requirement says "every book belongs to a category", the column must be NOT NULL and a book without a category must not be saved.

The polymorphic association that appears when a foreign key needs to point to one of several tables (such as a comment that can be on a book or on an author) is a separate problem; its options are in the "Polymorphic Association" post of the Çözüm Desenleri series.

In Production

Think about an index for every foreign key column. SQL Server does not create an index on the child side automatically when you define a foreign key. When a row is deleted from the parent or its key is updated, the engine looks for matching rows in the child table; without an index this search scans the whole table and holds locks longer. Index design is in the "Nonclustered Index Tasarımı" post of the SQL Server Derinlemesine series.

Map your cascade chains. ON DELETE CASCADE can spread across several levels, and a single DELETE can remove far more rows than you expect. SQL Server already rejects definitions where several cascade paths reach the same table; but seeing how long an allowed chain gets is your job.

Changing the primary key of an associative entity is a migration project. Rebuilding the clustered index, the dependent foreign keys and the queries in the application change together. How to make this change on a live system without downtime is in the "Migration'ların Deploy'daki Yeri" post of the Deployment Stratejileri series.

Common Pitfalls

Not deciding what a nullable foreign key means. As we saw above, NULL drifts between "none" and "unknown"; if which one is not written down, every report makes its own interpretation.

Building an M-N table as only two foreign keys and adding attributes later. If the identity of the relationship changes, the primary key changes too, and that is expensive on a live table.

Splitting a 1-1 for no reason. If there is no large and optional data, no security split and no subtype, two tables only mean one more join on every read and one more consistency problem on every write.

Splitting a ternary relationship into two binary ones. The split tables look correct one by one; the error shows up only when they are joined, as spurious rows.

Leaving delete behaviour to the default. NO ACTION and CASCADE are both decisions; whoever writes the foreign key should be able to say which one and why.

Relying only on the foreign key in a self-reference. A foreign key blocks a parent that does not exist, but not a cycle.

A Small Experiment

★☆☆ Add a row to the book_details table, then redefine the FK_book_details_books foreign key with ON DELETE CASCADE. Is cascade consistent with the model for this relationship? Is book_details a weak entity?

★★☆ Write an AFTER UPDATE trigger on the categories table that prevents cycles: walk upwards from the updated row with a recursive CTE and roll the transaction back if you reach the same category_id again. Try the Fiction–Fantasy cycle above again.

★★★ Change the rule in the publishing_rights table: a book can now have several publishers in a country (for different formats). What should the primary key be? Repeat the same split-and-join experiment with the new data; do spurious rows still appear?

We have taken relationships down to tables; at every step we made a key decision but never questioned the key itself. In the next post we discuss the chain from superkey to candidate key, natural key to surrogate key and composite key, the traps of "natural" key candidates such as ISBN (International Standard Book Number), email and national ID numbers, and the physical type decision.