Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Sydney Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •