15

I want to implement one-to-many concept in my application. This is the scenario: I have two tables

(i). Person(ID, NAME, AGE, DEPT)
(ii). Person Responsibilities(ID, RESP'S)

One person may have more than one responsibility. How shall I implement a 1-n relationship here? Actually, I don't understand the correct concepts for this.

Any suggestions or links to understand this concept would be appreciated.

philipxy
  • 14,867
  • 6
  • 39
  • 83

3 Answers3

22

This one-to-many relationship can be interpreted in plain English like this...

A Person has one or more responsibilities,

AND

Each responsibility belongs to exactly one person.

Now depending on which rdbms you're using, you would implement this as a foreign key relationship.

First you need to add a column to RESPS that points to the person table.

Let's call this new column PERSON_ID.

Now we can declare the relationship, the code might look something like this;

ALTER TABLE [Responsibilities] ADD CONSTRAINT FOREIGN KEY (PERSON_ID) 
REFERENCES [Person] (ID)

And this declaration of a foreign key constraint would mean that from now on you cannot add a responsibility without specifying a person who owns that responsibility.

But you could still add a person with no responsibilities (yet) since there are no constraints on the person table.

Note that this is all kind of academic, since in real life responsibilities are shared.

In other words, a person might have one or more responsibilities, but each responsibility might belong to one or more persons.

That's called a many-to-many relationship, and is a well-known database design issue with a well defined solution - which I won't go into now since it's tangential to your question.

Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
  • For one to many relationship above, Responsibilities will have PersonID as foreign key. Can Person table have ResponsibilityID as foreign key as well ? It does not sound right to me, but i need to know the reason why it is wrong to have foreign keys onto both parent and child tables.. – ATHER Dec 31 '14 at 19:32
1

I would simply say that the backward relation of a one-to-many is a many-to-one (which is a foreign key). Thus, to implement a one-to-many, you have to use a foreign key on the related table.

Antoine Pinsard
  • 33,148
  • 8
  • 67
  • 87
  • Right, the direction of the dependency matters. With a foreign key, you can achieve a many-to-one relationship (or a one-to-one), but not a one-to-many. – the_prole Jul 06 '22 at 05:53
0

What you'll eventually have is a table the consists of two columns (for the responsibilities only):

PersonId, Responsibility

And so you may have something like

1000 ,TakeCareOfGraden
1000 ,TakeCareOfServerRoom

Which means a person with PersonId =1000 is responsible for both.

The key here is a composite key that includes both fields (since neither is unique).

Oren A
  • 5,870
  • 6
  • 43
  • 64