Model more complex relationships

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

The current database model

  • 1:N-relationships are implemented with one foreign key
Introduction to Relational Databases in SQL

The final database model

Introduction to Relational Databases in SQL

How to implement N:M-relationships

  • Create a table
  • Add foreign keys for every connected table
  • Add additional attributes
CREATE TABLE affiliations (
 professor_id integer REFERENCES professors (id),
 organization_id varchar(256) REFERENCES organizations (id),
 function varchar(256)
);
  • No primary key!
  • Possible PK = {professor_id, organization_id, function}
Introduction to Relational Databases in SQL

Time to implement this!

Introduction to Relational Databases in SQL

Preparing Video For Download...