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 resp's. How shall i implement 1-n Relationship here. Actually, i cant able to understand the correct concept of this.
Any suggestions or links to understand its concept would be appreciative..
Thanks in Advance.
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 responsbility.
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.
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).