How to Implement One-To-One, One-To-Many and Many-To-Many Relationships While Designing Tables

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

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

Is it better to implement one-to-many relationship using tables as arrays?

In general, SQL works far better with a small number of tables than it does with a design that calls for a new table for each instance of a particular entity.

In your case the entity in question is the album. Put all your albums in one table with separate id values, according to your first design.

A query of the form

SELECT Track.Name, Album.Name
  FROM Track
  JOIN Album ON Track.Album_ID = Album.ID
 WHERE Album.Name = 'Revolver' 

is astonishingly efficient. It will exploit an SQL index on the Album_ID column. If the index is present it will avoid the full table scan ("processing ones with non-matching IDs") you mention in your question.

There's another problem with your second approach: In SQL queries, the names of tables must be constant. You cannot use the value of a column as the name of a table (or column or database).

Thousands (truly) of years of programmer time have gone into making SQL queries efficient. There's almost nothing left to be gained from trying to outsmart the query optimizer software in MySQL Or other RDBMS software.

How to implement ER Relationships :One to one, One To Many, Many to Many in Oracle?

An example of a many-to-many relationship would be EMPLOYEES and SKILLS, where SKILLS are things like "SQL", "Javascript", "Management" etc. An employee may have many skills (e.g. may know SQL and Javascript), and a particular skill by be possessed by many employees (e.g. Jack and Jill both know SQL).

In a database like Oracle, you need a third table to express the many-to-many relationship between EMPLOYEES and SKILLS:

create table EMPLOYEE_SKILLS
  ( empid references EMPLOYEES
  , skillid references SKILLS
  , constraint EMPLOYEE_SKILLS_PK primary key (empid, skillid)
  );

Note that this third table has a foreign key to both of the other tables.

The table can also hold further information about the relationship - for example:

create table EMPLOYEE_SKILLS
  ( empid references EMPLOYEES
  , skillid references SKILLS
  , rating number
  , date_certified date
  , constraint EMPLOYEE_SKILLS_PK primary key (empid, skillid)
  );

How to create one to many relationship from many to many join table in Prisma

I just need to adjust the UserProgram model a bit to account for multi-field id in the ProgramEnrollment model.

model ProgramEnrollment {
  program                 Program     @relation(fields: [programId], references: [id])
  programId               Int // relation scalar field (used in the `@relation` attribute above)
  user                    User        @relation(fields: [userId], references: [id])
  userId                  Int // relation scalar field (used in the `@relation` attribute            
  assignedAt              DateTime    @default(now())
  userProgram             UserProgram[] 

  @@id([programId, userId])
}

model UserProgram {
  id                           Int                 @id @default(autoincrement())
  name                         String
  userProgramEnrollment        ProgramEnrollment @relation(fields: [programEnrollment_programId,  programEnrollment_userId], references: [programId, userId])
  programEnrollment_programId  Int
  programEnrollment_userId     Int 
} 

Since ProgramEnrollment uses two fields for its id, we have to reference both of them in the UserProgram model.

One-to-One and One-to-Many relation between same 2 table

-- Item ITM is priced at PRI dollars.
--
item {ITM, PRI}
  PK {ITM}

-- All items in group GRP
-- are priced at PRG dollars.
--
_group {GRP, PRG}
    PK {GRP}

-- Item ITM is member of group GRP.
--
item_group {ITM, GRP}
        PK {ITM}

FK1 {ITM} REFERENCES  item  {ITM}
FK2 {GRP} REFERENCES _group {GRP}

If an item is member of the group,
then the group price applies; otherwise the item price applies.

-- Item ITM is priced at PRICE dollars.
--
CREATE VIEW item_price
AS
SELECT i.ITM
     , coalesce (g.PRG, i.PRI) AS PRICE
FROM       item       AS i
LEFT JOIN  item_group AS x ON x.ITM = i.ITM
LEFT JOIN _group      AS g ON g.GRP = x.GRP ;

Notes

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key

---

Option 2

As per comments below, to keep GRP in item.

-- All items in group GRP
-- are priced at group-price of PRG dollars.
--
_group {GRP, PRG}
    PK {GRP}

-- Sample data
--
(GRP, PRG)
----------
(G00, -1)  -- special group, individual item price applies
(G01, 120)
(G02, 150)

-- Item ITM, from group GRP, is priced at 
-- individual item price of PRI dollars.
--
item {ITM, GRP, PRI}
  PK {ITM}

If an item is member of the group G00, then the item price applies, otherwise the group price applies.

-- Item ITM is priced at PRICE dollars.
--
CREATE VIEW item_price
AS
SELECT i.ITM
     , CASE WHEN i.GRP = 'G00' THEN i.PRI
                               ELSE g.PRG
       END  AS PRICE
FROM  item  AS i
JOIN _group AS g ON g.GRP = i.GRP ;

Difference between one-to-many and many-to-one relationship

Yes, it a vice versa. It depends on which side of the relationship the entity is present on.



For example, if one department can employ for several employees then, department to employee is a one to many relationship (1 department employs many employees), while employee to department relationship is many to one (many employees work in one department).

More info on the relationship types:

Database Relationships - IBM DB2 documentation

---

Related Topics