Database Design – a 2-to-many relationship! (Access any version)

Topic

New Reply

i am designing a soccer club database, and want to track kids (players) and parents’ details.

the relationship between the kids table and parents table has me in a quandry – do i treat it as a many to many and include a junction table, or do i split “parents” into two tables, and have one father record to many kids, and one mother record to many kids?

i would be very grateful for some advice. thanks, Lisa

Reply | Quote

Replies

I would look at it as one table – one for the players and the players contact(s). I guess it is not a prerequisite for every player to have two – (most often mother and father – but could be just one or even grand father/ uncle/guardian etc).
In the players table have fields for all players attributes and then something like Contact1Name/Contact1Address1/…Address2/…TelNo/ etc and Contact2Name/Contact2Address1/…Address2/…TelNo etc . Allow the contact two details to have a value of as above where the two contacts share details and leave it like that.

i think you may have been trying to go too far down the normalisation route, sometimes absolute efficiency in data duplication can be more trouble to implement than the reward of a ‘faster/more efficient’ database.

When i get a problem like this i also remember a quote in a developer book that i have that states:
“Database design is more of an art than a science”
– you cant always apply every rule absolutely

hope this helps

Reply | Quote

I agree with fatherjack. Sometimes we get too hung up in the details. This application lends itself to 2 tables – Kids and Parents. Each kid should have a primary contact, which could be your key and would establish the relationship between the 2 tables. If necessary, you could then accommodate additional contacts in your parents table by creating additional fields for that information.

Reply | Quote

Agreed – the normalization rules are good guidance, but reality often intrudes. One of the unusual traits of Access is the ability to do self-joins on a table. We don’t often use it, but it could be applied here if you think of everyone as a person and simply have a person table with pointers to parent records if you are a child. Just another way of approaching the problem. Hope your project goes well.

Reply | Quote