CODEWITHSUNDEEP
sqloracle-database
Himanshu
Himanshu

Reputation: 3970

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

Perhaps, this could seem like the basics of database design.But,certainly i find these concepts a lil tricky in the sense that how they relate to each other.

Theory as per my understanding.

One to One : When each particular entity has one record.

One to Many : When each particular entity has many record

Many to Many :

Multiple entities have multiple records.

As per the above if i could relate an example as

  1. ONE TO ONE

Each employee having a unique passport number.

Table Employee

Empid(pk) 
empname 
passpordid(fk to passport)

Table passport

passportid(pk) 
passportno
  1. ONE TO MANY

An organisation having multiple employees

TABLE ORGANISATION

ORGID (PK) 
ORGNAME
EMPID (FK TO EMPLOYEE)

TABLE EMPLOYEE 

EMPID (PK) 
EMPNAME
SALARY

This is the part that i want to know more that is many to many. I mean if we see one to many here. As a whole it could be said as many to many as many organisations having many employees but does that mean the whole relationship is many to many not one to many or one to many is a subset of many to many in the above example.

I wanna know the difference mainly between one to many and many to many both theoritically and by implementation.

Upvotes: 0

Views: 193

Answers (3)

JCampy
JCampy

Reputation: 191

You seem to have the basic idea of one-to-one and one-to many down, in theory.

One-to-one: each item A has a separate and unique item B

In a database, you could include the new data in the same table. You could put it in a separate table, and enforce the one-to-one constraint by making the id into the remote table "unique" (indexing constraint), forcing the id for item B to not be repeated in the table. In your example, this would be a constraint added to the passportid.

One to many: each item A has some number of item Bs

In a database, you put the key in the table on the "many" side. So many Bs would have the same foreign key into table A. In your example, put an orgid as a foireign key into the employee table. Many employees can then point to a single organization, and an employee can belong to one organization.

Many to many: some number of As can be linked to any number of Bs and vice-versa

In a database, this is usually implemented as a link table, having just the IDs from both A and B. Following you example, consider that an employee can be part of multiple groups (or projects), multitasking across those groups. You could have a group table:

GROUPID (PK) GROUPNAME

And the link table would then look like

LINKID (PK) EMPID (FK TO EMPLOYEE) GROUPID (FK TO GROUP)

This lets any employee be part of any number of groups, and groups to have any number of employees, AKA many-to-many.

A side comment:

Technically, you don't HAVE to have a LINKID in the link table. But it makes it easier to manage the data in that table, and is generally considered "best practice".

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132700

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

Upvotes: 2

TheMouseMaster
TheMouseMaster

Reputation: 298

This is more a theory question that a programming one, but why not.

You could imagine a "many to many" as 1 table being a list of employees, and another table being a list of products sold.

Each employee is likely to "handle" more than 1 product, and each product could very well be handled by multiple employees, and it's quite possible that products A and B can be handled by, at the same time, employees C and D.

Upvotes: 0

Related Questions