How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

Question

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

Answer 1

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a                                                         # "link back" if you need 

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side classes:  class_id, class_name, teacher_id  # the "many" side 

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name classes: class_id, name, teacher_id student_classes: class_id, student_id     # the junction table 

Example queries:

 -- Getting all students for a class:      SELECT s.student_id, last_name       FROM student_classes sc  INNER JOIN students s ON s.student_id = sc.student_id      WHERE sc.class_id = X   -- Getting all classes for a student:       SELECT c.class_id, name       FROM student_classes sc  INNER JOIN classes c ON c.class_id = sc.class_id      WHERE sc.student_id = Y 

Answer 2

Here are some real-world examples of the types of relationships:

One-to-one (1:1)

A relationship is one-to-one if and only if one record from table A is related to a maximum of one record in table B.

To establish a one-to-one relationship, the primary key of table B (with no orphan record) must be the secondary key of table A (with orphan records).

For example:

CREATE TABLE Gov(     GID number(6) PRIMARY KEY,      Name varchar2(25),      Address varchar2(30),      TermBegin date,     TermEnd date );   CREATE TABLE State(     SID number(3) PRIMARY KEY,     StateName varchar2(15),     Population number(10),     SGID Number(4) REFERENCES Gov(GID),      CONSTRAINT GOV_SDID UNIQUE (SGID) );  INSERT INTO gov(GID, Name, Address, TermBegin)  values(110, 'Bob', '123 Any St', '1-Jan-2009');  INSERT INTO STATE values(111, 'Virginia', 2000000, 110); 

One-to-many (1:M)

A relationship is one-to-many if and only if one record from table A is related to one or more records in table B. However, one record in table B cannot be related to more than one record in table A.

To establish a one-to-many relationship, the primary key of table A (the "one" table) must be the secondary key of table B (the "many" table).

For example:

CREATE TABLE Vendor(     VendorNumber number(4) PRIMARY KEY,     Name varchar2(20),     Address varchar2(20),     City varchar2(15),     Street varchar2(2),     ZipCode varchar2(10),     Contact varchar2(16),     PhoneNumber varchar2(12),     Status varchar2(8),     StampDate date );  CREATE TABLE Inventory(     Item varchar2(6) PRIMARY KEY,     Description varchar2(30),     CurrentQuantity number(4) NOT NULL,     VendorNumber number(2) REFERENCES Vendor(VendorNumber),     ReorderQuantity number(3) NOT NULL ); 

Many-to-many (M:M)

A relationship is many-to-many if and only if one record from table A is related to one or more records in table B and vice-versa.

To establish a many-to-many relationship, create a third table called "ClassStudentRelation" which will have the primary keys of both table A and table B.

CREATE TABLE Class(     ClassID varchar2(10) PRIMARY KEY,      Title varchar2(30),     Instructor varchar2(30),      Day varchar2(15),      Time varchar2(10) );  CREATE TABLE Student(     StudentID varchar2(15) PRIMARY KEY,      Name varchar2(35),     Major varchar2(35),      ClassYear varchar2(10),      Status varchar2(10) );    CREATE TABLE ClassStudentRelation(     StudentID varchar2(15) NOT NULL,     ClassID varchar2(14) NOT NULL,     FOREIGN KEY (StudentID) REFERENCES Student(StudentID),      FOREIGN KEY (ClassID) REFERENCES Class(ClassID),     UNIQUE (StudentID, ClassID) );