By arsenal

2011-09-04 01:17:36 8 Comments

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


@Arabinda Banik 2016-04-14 18:34:43

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:

    GID number(6) PRIMARY KEY, 
    Name varchar2(25), 
    Address varchar2(30), 
    TermBegin date,
    TermEnd date

    SID number(3) PRIMARY KEY,
    StateName varchar2(15),
    Population number(10),
    SGID Number(4) REFERENCES Gov(GID), 

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 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:

    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

    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.

    ClassID varchar2(10) PRIMARY KEY, 
    Title varchar2(30),
    Instructor varchar2(30), 
    Day varchar2(15), 
    Time varchar2(10)

    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), 
    UNIQUE (StudentID, ClassID)

@obeliksz 2018-12-07 11:02:23

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)...

@moo cow 2018-12-18 08:36:59

@obeliksz could be nulls?

@strix25 2019-04-16 09:43:37

Why would you use UNIQUE (StudentID, ClassID) on the end of M:N ?

@FouadDev 2019-05-04 19:18:47

@strix25 To enforce avoiding repetition in creating the same ClassStudentRelation row multiple times, because if you don't make sure both foreign keys StudentID and ClassID are unique, what stops creating a new row with the same StudentID and ClassID ? as they are not unique in the code above. So you either implement it like the code above, or add a primary key that includes both StudentID and ClassID to avoid repetition of creating the same row in ClassStudentRelation.

@valik 2019-06-28 07:01:39

@FouadDev Basically you are saying to strix25 that although we have many to many, between class and student, we should not be able to create (james has biology and biology has james ) more than once. since it already exists.

@FouadDev 2019-07-11 23:09:23

@valik Data in databases work by referencing existing data, and not creating the same piece of data multiple times, why would you do that? of course you don't have to, otherwise it's not efficient. With that in mind, let's go back to your example (james has biology and biology has james), Of course you can, BUT without creating another piece of data that already exist in database. All you need to do is to just reference the already existing one whenever you want to create any relationship. I hope that helps :)

@NullUserException 2011-09-04 01:24:07

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

@dev_feed 2014-05-29 15:32:15

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.

@edhedges 2014-09-06 23:23:24

@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.

@user2166164 2015-05-23 12:57:27

@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.

@user2166164 2015-05-23 13:01:13

@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..

@Cody 2015-07-06 16:56:49

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 (?).

@NullUserException 2015-07-06 16:59:22

@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.

@Cody 2015-07-06 17:04:53

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!

@NullUserException 2015-07-06 17:14:53

@HLGEM 2016-04-14 18:54:35

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.

@Omar Tariq 2017-03-26 11:10:44

There can be multiple students from the same house. People have more than one kid these days.

@JMRC 2017-12-18 14:09:09

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).

@Zingam 2018-02-20 15:15:06

@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.

@Alex78191 2018-04-15 18:41:59

How to implement One-to-one-or-more?

@KGhatak 2019-03-27 06:08:39

@ NullUserException - The one-to-many example seems ambiguous. One class can have many teachers, and one teacher can teach in many classes - isn't it a case of many-to-many. Rather, 'dir' and 'file' in a file-system are more natural examples of one-to-many.

@jpmc26 2019-05-07 22:27:08

Junction tables go by several names. Some other examples are bridge table and associative entity.

@Bradley Kreider 2019-06-04 16:05:28

@omarTariq: > "There can be multiple students from the same house" Notice the address record has a primary id, so addresses don't have to be unique. the only reason they need to be split out is so they can easily change and perhaps it becomes 1-M to track address history.

@Anjan Kant 2016-05-16 10:41:01

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
   CallerPhoneNumber CHAR(10) NOT NULL
-- This table will hold our "tickets" (or cases).
CREATE TABLE dbo.Tickets
   Subject VARCHAR(250) NOT NULL,
   Notes VARCHAR(8000) NOT NULL,
-- This table will link a phone call with a ticket.
CREATE TABLE dbo.PhoneCalls_Tickets
   PhoneCallID INT NOT NULL,

@Ashish K Gupta 2017-07-13 09:55:30

Would have been better & more clear if you had added primary key and foreign key constraints as well.

Related Questions

Sponsored Content

7 Answered Questions

8 Answered Questions

[SOLVED] How does database indexing work?

37 Answered Questions

10 Answered Questions

[SOLVED] How do I escape a single quote in SQL Server?

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

27 Answered Questions

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

23 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

12 Answered Questions

[SOLVED] When should I use cross apply over inner join?

1 Answered Questions

[SOLVED] How to implement a many-to-many relationship in PostgreSQL?

Sponsored Content