Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
Here are some real-world examples for the types of relationships:
A relationship is one to one if and only if one record from table A is related to a
maximum of one record in a 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 the table A (with orphan records).
CREATE TABLE Gov(
GID number(6) PRIMARY KEY,
CREATE TABLE State(
SID number(3) PRIMARY KEY,
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);
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 can not be related to more 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).
CREATE TABLE Vendor(
VendorNumber number(4) PRIMARY KEY,
CREATE TABLE Inventory(
Item varchar2(6) PRIMARY KEY,
CurrentQuantity number(4) NOT NULL,
VendorNumber number(2) REFERENCES Vendor(VendorNumber),
ReorderQuantity number(3) NOT NULL
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 the primary keys of both table A and table B.
CREATE TABLE Class(
ClassID varchar2(10) PRIMARY KEY,
CREATE TABLE Student(
StudentID varchar2(15) PRIMARY KEY,
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)
1st example: GID number(6) and SGID Number(4), why? Shouldn't SGID also be (6)? And at 2nd example number(4) and number(2)...
@obeliksz could be nulls?
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
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
-- 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
What's a good example of when the "link back" is useful in the One-to-One relationship? Thanks for the clear and concise answer.
@dev_feed In terms of database design I don't see the link back to be beneficial, but using the example above the link back could simplify finding a student given an address.
@NullUserException do we must need 3 tables for Many-to-many relationship.can't it be done by two tables the Many-to-many Relationship.
@NullUserException i need a litte bit assistance can you guide me sir. i have table task_tbl and i want user to rate a task i am a littlle bit nervous how to do it should i create another table the rate_tbl or should i put it in task_tbl with a cloum name rate.Thanks in advance sir..
Each student_classes row would have 2 One-To-One relationships, right? One student row has many classes and one classes row has many students -- but one student_classes row has only one to each of them (?).
@Cody Each student_classes row should only have one one-to-one relationship. If studentA is in classA and classB, then there should be two rows in student_classes, one for which relationship.
But each one of those would also have a relationship to a class -- another one-to-one, right? I'm playing devil's advocate a little, but I'll have to roll this one around and defer to your judgement (I'm more of a front-end dev). Thx!
@Cody See this example: gist.github.com/anonymous/79c2eed2a634777b16ff
In a one to one relationship, the join field should be unique in both tables. It is likely a PK on one table which guarantees uniqueness, but it may need a unique index on the other table.
There can be multiple students from the same house. People have more than one kid these days.
Like @HLGEM said, with 1:1, you might want to make the field unique or even use the foreign key to automatically remove one record if the corresponding one is removed as well. But you might want to consider just putting them in one table if both data is accessed frequently (maybe student and address), except if it jeopardizes your security (probably buyer and credit card info).
@NullUserException Is it possible to a Many-to-Many relationships within the same table. Let's say a the junction table contains the relationships between the students.
How to implement One-to-one-or-more?
One to one (1-1) relationship:
This is relationship between primary & foreign key (primary key relating to foreign key only one record). this is one to one relationship.
One to Many (1-M) relationship:
This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records (i.e. Table A have book info and Table B have multiple publishers of one book).
Many to Many (M-M): Many to many includes two dimensions, explained fully as below with sample.
-- This table will hold our phone calls.
CREATE TABLE dbo.PhoneCalls
ID INT IDENTITY(1, 1) NOT NULL,
CallTime DATETIME NOT NULL DEFAULT GETDATE(),
CallerPhoneNumber CHAR(10) NOT NULL
-- This table will hold our "tickets" (or cases).
CREATE TABLE dbo.Tickets
ID INT IDENTITY(1, 1) NOT NULL,
CreatedTime DATETIME NOT NULL DEFAULT GETDATE(),
Subject VARCHAR(250) NOT NULL,
Notes VARCHAR(8000) NOT NULL,
Completed BIT NOT NULL DEFAULT 0
-- This table will link a phone call with a ticket.
CREATE TABLE dbo.PhoneCalls_Tickets
PhoneCallID INT NOT NULL,
TicketID INT NOT NULL
Would have been better & more clear if you had added primary key and foreign key constraints as well.