Page 1 of 1
MS Access Table and relationship
Posted: Tue Jan 27, 2004 9:50 am
by szms
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.
Posted: Tue Jan 27, 2004 10:51 am
by xisle
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
Posted: Tue Jan 27, 2004 11:55 am
by szms
For Option 1:
Why did you use Guardian_ID and Child_ID as optional?
Posted: Tue Jan 27, 2004 1:50 pm
by xisle
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...
Posted: Wed Jan 28, 2004 7:48 am
by szms
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?
Posted: Mon Feb 23, 2004 1:03 pm
by szms
Hell Again.
how do I implement those 3 tables using MS Access. I having problem with creating realtionships. As I cannot do it properly, I am unable to entry data using form. Could you please give me detail information about those 3 table implementation using MS Access. Thank you.
Posted: Thu Mar 25, 2004 8:43 am
by szms
is there anyone who cqan send me a moke up for this database problem. Thank you.
Posted: Thu Apr 01, 2004 11:16 am
by mikefabro