The ER model says what to store. These rules turn it into tables:
| ER model | Tables |
|---|---|
| entity set | a table; the key attribute is the primary key |
| simple attribute | a column |
| composite attribute | one column per part (street, city) |
| multivalued attribute | its own table: (entity key, value) |
| derived attribute | no column; calculate it in a query |
| 1 : N relationship | a foreign key column on the N side |
| 1 : 1 relationship | a foreign key on either side, with UNIQUE |
| M : N relationship | a junction table with both keys |
| total participation on the N side | the foreign key is also NOT NULL |
An example
publisher
1 : N
book
M : N
author
The 1 : N relationship becomes a foreign key in book:
book
| book_id | title | publisher_id |
|---|---|---|
| 1 | Query Craft | 10 |
| 2 | Keys and Locks | 10 |
| 3 | Index Notes | 20 |
The M : N relationship becomes a table of pairs. Its primary key is the pair:
book_author
| book_id | author_id |
|---|---|
| 1 | 7 |
| 1 | 8 |
| 2 | 7 |
sql
CREATE TABLE book_author (
book_id integer REFERENCES book (book_id),
author_id integer REFERENCES author (author_id),
PRIMARY KEY (book_id, author_id)
);Attributes of the relationship, such as the order of the authors on the cover, are columns of the junction table.