ER Model to Tables

ER Model to Tables

Translating an ER Model into Tables

Each entity becomes a table whose key is its primary key. A 1:N relationship becomes a foreign key on the "many" side (orders.customer_id). A 1:1 becomes a foreign key with a UNIQUE constraint on the optional side, or one merged table. An N:M becomes a junction table with a foreign key to each side and the pair as its primary key, and so does a multi-valued attribute whose values have facts of their own. order_items is the shop's junction table, and the sheet's authors need another:

Books and authors, an N:M relationship, through a junction tableSQL

CREATE TABLE authors (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE product_authors (
  product_id INT UNSIGNED NOT NULL, author_id INT UNSIGNED NOT NULL,
  position   TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (product_id, author_id),
  FOREIGN KEY (product_id) REFERENCES products (id),
  FOREIGN KEY (author_id)  REFERENCES authors (id)
);

The pair key forbids listing an author twice on a book, and position (byline order) describes the pairing. MySQL 524 adds an index named author_id for the foreign key, so both directions are fast.

MENU