MS Access Table and relationship

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
szms
Forum Contributor
Posts: 101
Joined: Thu Jun 26, 2003 12:23 pm

MS Access Table and relationship

Post 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.
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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
szms
Forum Contributor
Posts: 101
Joined: Thu Jun 26, 2003 12:23 pm

Post by szms »

For Option 1:
Why did you use Guardian_ID and Child_ID as optional?
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post 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...
szms
Forum Contributor
Posts: 101
Joined: Thu Jun 26, 2003 12:23 pm

Post 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?
szms
Forum Contributor
Posts: 101
Joined: Thu Jun 26, 2003 12:23 pm

Post 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.
szms
Forum Contributor
Posts: 101
Joined: Thu Jun 26, 2003 12:23 pm

Post by szms »

is there anyone who cqan send me a moke up for this database problem. Thank you.
mikefabro
Forum Newbie
Posts: 1
Joined: Thu Apr 01, 2004 11:16 am
Location: Waterloo ON, CAN

Post by mikefabro »

check out this example in another great forum:

http://www.access-programmers.co.uk/for ... manytomany
Post Reply