How to implement ONE-TO-MANY in a database

Question

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.

Answer 1

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.