Many to Many to Many to Many

[Chance1234]

Heres one to mull over,

I have three tables

Children
-----------
CHILD_ID, NAME

Adults
------------
ADULT_ID, NAME

Event
------------
EVENT_ID, DATE, NAME

To start with I ll just take two of these tables

Children
Event

now a child can go to many events and many children can go to many events.

So simply i would create a link table in Access and have a one to many relationship going from children and event to the link table. no problem

Also bringing in the adults i would create a seperate link table to show the many to many, to events.

but what i was thinking was can i get away with one link table ?

I imagine it would look like this

EVENT_ID, CHILD_ID, ADULT_ID
1 2
1 3
1 6
1 2
1 5
1 7
2 1
2 3
2 5
2 1

and i can see from that, by using simple querys i can get most of the infomaiton i want such as how many children attended event 1 , i cane see which children has attended an event more than once etc etc etc

can anyone see any fault in this logic ? or potential problems

http://WWW.5YLAC.S5.COM

 

[johnnymac43]

This would work, but a better way would be to have a 'person' table for children and adults, linked to a 'person type' table with values of 'child' and 'adult'. It might also be worth setting up an 'event type' table as a form of insurance against changing requirements.

On the same line of thinking (allowing for an uncertain future) you could give your link table (event-person) a type field to cater for things other than attendance, such as sponsorship, patronage, official guest, etc.

HTH

John

 

[fneily]

Have one junction table with a three field primary key - event, child, adult. Then have a default of "None" for the child and adult fields. This way it will take care of any permutations. eg. event 1, none, none
event 1, child, none
event 1, child, adult
event 1, none, adult

Neil