Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Posts
    78

    Unanswered: Normalizing Data

    I am currently working on a database to track pedigrees. I asked for help previously and got some good ideas as to get recrods to update the current table. thank you for those who have helped. I did get some advice but not direction (or atleast none I understood) on how to use separate tables in three tables.

    Currently my Dog table has fields for Sire and Dam (Father and Mother). I have been reading up etc and this data would not be normalized as the Sire can be a father to many Dogs and same for the Dam being a mother to many dogs.) So they would be listed many times.

    I currently I have started to revamp my data set up and have created tblDogs with DogID and DogName as fields, tblSire with SireID and Sire as fields and tblDaam with DamID and Dam as fields. I am missing how to create the junction table or relate these however as to be able to fill the tables and keep them "normalized". If someone has time could you draw a picture on what I need to create? I have tried several ways and nothing has worked for me. This newbie is stuck. Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would not have three tables, i would have just one

    create table tblDogs
    ( DogID primary key
    , DogName
    , sireID foreign key references tblDogs (DogID)
    , damID foreign key references tblDogs (DogID)
    )

    you are right that a sire can be a father to many dogs and a dam can be a mother to many dogs, but from the point of view of the dog, it has only one of each, hence sire-to-dog and dam-to-dog are each a one-to-many relationship

    and since sires and dams are also dogs, the best way to handle the data is all in one table

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by r937
    i would not have three tables, i would have just one

    create table tblDogs
    ( DogID primary key
    , DogName
    , sireID foreign key references tblDogs (DogID)
    , damID foreign key references tblDogs (DogID)
    )

    you are right that a sire can be a father to many dogs and a dam can be a mother to many dogs, but from the point of view of the dog, it has only one of each, hence sire-to-dog and dam-to-dog are each a one-to-many relationship

    and since sires and dams are also dogs, the best way to handle the data is all in one table

    rudy
    http://rudy.ca/
    Thanx for the Information Rudy. . it is muchly appreciated and is what I am currently doing. It is good to have affirmation that you are doing something correctly.

    The db is working fine however after reading about creating databases etc the data won't be "normalized" as the Sire and Dam will be listed many times over as they may be parents to many dogs. I am trying to keep Database size down as Registered Names for Dogs can be reather lengthy . . and if listed many times the db will get large much more quickly. With people who do alot of breeding it would be important to keep the size of the db down as long as possible.

    On top of that they also put pedigrees of dogs they are interested in to help with their breeding program so as you can tell it could get rather large rather quickly. Each Pedigree (which is five genereations) is a total of 63 dogs. With that being said, do you have insight (or are willing to ) on how to use them in three different tables as to normalize the data and keep the db as small as possible for as long as possible.

    Thank you once again for your input. I am REALLY new to all of this and feel like a fish out of water. Is nice to know I am not totally off

    Thank you

    t

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look at the one-table solution again -- and it's normalized, too

    each sire's name is in there only once

    that is the best way to keep the size of the table as small as possible

    don't do it with three tables, you are only asking for trouble


  5. #5
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by r937
    look at the one-table solution again -- and it's normalized, too

    each sire's name is in there only once

    that is the best way to keep the size of the table as small as possible

    don't do it with three tables, you are only asking for trouble

    Actually I am missing something as I don't see how the data is normalized. If it is normalized it is in the db once correct???


    The only way it is going to happen is if a dog only gives birth to one dog or sire one dog their entire life. I am either missing something or a clue puppy.

    EX Stiles Precious Shadow gave birth to three dogs this past Christmas, The Sire was Ford's Jaker Breaker. So the entries for the children are as follows.


    Winchester Christmas Star, _____ Ford's Jaker Breaker, _____ Stile's Precious Shadow
    Winchester Christmas Noel, _____ Ford's Jaker Breaker, _____ Stile's Precious Shadow
    Winchester Christmas Angel, _____ Ford's Jaker Breaker, _____ Stile's Precious Shadow

    So how can thei be normalized if The Sire and Dam Ford's Jake Breaker and Stile's Precious Shadow are in the db Sire and Dam fields respectively three times? Again. . . just trying to understand. thank you for your help.. .
    Last edited by t_galownia; 09-19-02 at 16:50.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you missed the part in my first post about the foreign keys

    you would have to have foreign keys in three tables, too, but as i said, don't go there

    here's your data in one table:

    Code:
    DogID  DogName                     sireID  damID 
     745   Ford's Jaker Breaker
     937   Stile's Precious Shadow
    1023   Winchester Christmas Star    745     937  
    1024   Winchester Christmas Noel    745     937  
    1025   Winchester Christmas Angel   745     937

  7. #7
    Join Date
    Aug 2002
    Posts
    78
    Originally posted by r937
    i think you missed the part in my first post about the foreign keys

    you would have to have foreign keys in three tables, too, but as i said, don't go there

    here's your data in one table:

    Code:
    DogID  DogName                     sireID  damID 
     745   Ford's Jaker Breaker
     937   Stile's Precious Shadow
    1023   Winchester Christmas Star    745     937  
    1024   Winchester Christmas Noel    745     937  
    1025   Winchester Christmas Angel   745     937
    thank you I think I understand a little bit better. will have to work on it from this angle. Muchly appreciated. . . Also thank yoiu for drawing the picture. . . I needed that) Sorry for being pain. . . I hope to get over newbieness soon.
    Last edited by t_galownia; 09-19-02 at 17:24.

Posting Permissions

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