How to implement ONE-TO-MANY in a database

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

(i). P开发者_如何学Cerson(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.

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.

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.

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