I am using MS Access to build a database. I have 3 tables: Child, Name, Guardian.
These three tables has following attributes respectively.
Child: Child_ID, DOB
Name: Name_ID, First Name, Middle Name, Last Name.
Guardian: Guardian_ID, Employer
A Child can have more than 1 Guardian.
How can I link these three tables so that these three tables make sense. Name table contain both Child's and Guardian's name.
MS Access Table and relationship
Moderator: General Moderators
use foriegn keys...
Child: ID(primary), Name_ID, Guardian_ID(optional), DOB
Name: ID(primary), First Name, Middle Name, Last Name.
Guardian: ID(primary), Child_ID(optional), Name_ID, Employer
or create linking tables...
child_guardian: Child_ID, Guardian_ID
child_name: Child_ID,Name_ID
guardian_name: Guardian_ID,Name_ID
Child: ID(primary), Name_ID, Guardian_ID(optional), DOB
Name: ID(primary), First Name, Middle Name, Last Name.
Guardian: ID(primary), Child_ID(optional), Name_ID, Employer
or create linking tables...
child_guardian: Child_ID, Guardian_ID
child_name: Child_ID,Name_ID
guardian_name: Guardian_ID,Name_ID
it really depends on how you will be searching the data and retrieving
it..maybe write a couple of select statements to make sure you have all
possibilities covered.
Also, if a child can have more than one guardian or vice versa, you may
want to store a string of guardian_IDs/child_IDs in a VarChar.
Or if there is a limit of say four guardians per child, make 4 guardian id fields(this is when linking tables become more efficient).
hope this helps...
it..maybe write a couple of select statements to make sure you have all
possibilities covered.
Also, if a child can have more than one guardian or vice versa, you may
want to store a string of guardian_IDs/child_IDs in a VarChar.
Or if there is a limit of say four guardians per child, make 4 guardian id fields(this is when linking tables become more efficient).
hope this helps...
How about the following idea:
CHILD TABLE:
Child_ID(PK)
DOB
First Name
Middle Name
Last Name
GUARDIAN TABLE:
Guardian_ID (PK)
First Name
Middle Name
Last Name
Employer
Link table:
Child_ID(PK/FK)
Guardian_ID (PK/FK)
But I I want to make another table for Name (First, Middle, Last) so that I don't need these 3 fields both in Child and Guardian table. What would be the relationship. I am not sure how to implement it in MS Access as well. Could you please help me?
CHILD TABLE:
Child_ID(PK)
DOB
First Name
Middle Name
Last Name
GUARDIAN TABLE:
Guardian_ID (PK)
First Name
Middle Name
Last Name
Employer
Link table:
Child_ID(PK/FK)
Guardian_ID (PK/FK)
But I I want to make another table for Name (First, Middle, Last) so that I don't need these 3 fields both in Child and Guardian table. What would be the relationship. I am not sure how to implement it in MS Access as well. Could you please help me?
check out this example in another great forum:
http://www.access-programmers.co.uk/for ... manytomany
http://www.access-programmers.co.uk/for ... manytomany