Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Pedigree design in a database

    Please bear with me as I am a complete newbie to database design. I am building a site that will track pets information, and more importantly at the moment, pedigree. So here is what I have so far:

    table pet
    petID (INT,PK)
    name (VARCHAR)
    sex (VARCHAR)
    breeder (VARCHAR)
    ...

    table pedigree
    pedigreeID (INT,PK)
    petID (from table pet)
    sire_petID (from table pet)
    dam_petID (from table pet)

    Now, from here I start to get confused as when I try to pull the data out I am trying to join three sets of information from the same table with different relationships which is just not working out for me (I get errors). To add to that I would like to go back three generations, which means adding on to the pedigree table or leading it back to itself. This is probably a really stupid question, sorry, does anyone have any suggestions on how to do this? Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    No, you've got exactly the right idea. The trick is that you need to join the pedigree table to itself to construct the actual pedigree. I'd strongly recommend using outer joins, since you won't always have some data to report. A query for two generations might end up looking something like:
    Code:
    SELECT *
       FROM pedigree AS this
       LEFT JOIN pedigree AS sire
          ON (sire.petID = this.sire_petID)
       LEFT JOIN pedigree AS dam
          ON (dam.petID = this.dam_petID)
       LEFT JOIN pedigree AS siresire
          ON (siresire.petID = sire.sire_petID)
       LEFT JOIN pedigree AS siredam
          ON (siredam.petID = sire.dam_petID)
       LEFT JOIN pedigree AS damsire
          ON (damsire.petID = dam.sire_petID)
       LEFT JOIN pedigree AS damdam
          ON (damdam.petID = dam.dam_petID)
    -PatP

  3. #3
    Join Date
    Dec 2004
    Posts
    54

    Rufff Rufffff

    Hi Jen,
    I started a conceptual model for you that 'I believe' captures some of the key relationships between your business entities.

    - Animal -
    - Breed -
    - People -

    You can fill in the attribution.

    Vmusic
    ~I never met a business problem I couldn't stick in a database~
    Attached Thumbnails Attached Thumbnails animals.gif  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    note that since a pet can have only one sire and one dam (if more, then i don't understand animal husbandry)

    so you could actually combine the pet and pedigree table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Posts
    54

    Ooops

    Hey Jen,
    I was about ready to call it a night. In a rush on your conceptual model, I made an error.

    OK... I redo the same model for each post, but I didn't change the relationship.

    See attached picture.

    On the sire and dam thing. I'm from Cleveland Ohio and uh, I don't know much about breeding animals...... they do that in the inner city it seems a lot.

    BUT... any animal may have one male and one female parent thus..... animal is related to more than one (2) animals ..... their parents.

    I'm guessing you may or may not ..... have knowledge of the parents.

    Enjoy!!
    Vmusic
    Attached Thumbnails Attached Thumbnails animals.gif  

  6. #6
    Join Date
    Jan 2005
    Posts
    2
    Thanks!!! Pat - one question if you dont mind, when you say "this" is that literal or meaning that petID? I did'nt include the whole structure for the sake of brevity and trying not to ask too many stupid questions at once. As far as the breed goes, I am only using registered animals so they can only have one breed, but they may have more than one registry - as with the breeder, there can only be one as that is defined as who owned the dam at the time of breeding (not the necessarily the owner) and that name is always printed on the pedigree even if the pet has changed owners ten times. It is true that each pet can only have one sire and dam and could be in the same table - would that make the joins more confusing or would it be the same as Pat outlined???

    OK, here is a more complete version - I'm on my work computer so I'm going by memory, sorry:

    table user
    userID (INT, PK)
    user_name (VARCHAR)
    password
    type (VARCHAR)
    user_namefirst (VARCHAR)
    user_namelast (VARCHAR)
    more fields...

    table pet
    petID (INT, PK)
    name (VARCHAR)
    sex (VARCHAR)
    breed (VARCHAR)
    dateofbirth (4, INT)
    thumbnail (VARCHAR)
    description (TEXT)
    breeder (VARCHAR)
    userID (from table user)

    table registry
    registryID (INT, PK)
    registry_name (VARCHAR)
    registry_address (VARCHAR)
    more fields...

    table registrydetail
    petID (from table pet)
    registryID (from table registry)
    designation (VARCHAR)

    table pedigree
    petID (from table pet)
    sire_petID (from table pet)
    dam_petID (from table pet)

    table photo
    photoID (INT, PK)
    photo_name (VARCHAR)
    photo_description (TEXT)
    petID (from table pet)

    Thats as close as I can get going from memory. Any suggestions? Thanks so much for the help so far!

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Quote Originally Posted by r937
    note that since a pet can have only one sire and one dam (if more, then i don't understand animal husbandry)

    so you could actually combine the pet and pedigree table
    This needs to be quoted.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I used the alias "this" to indicate the animal to which the current row corresponds. You'll generate one row for each animal in the pedigree table using the sample I showed, and for each row there is "this" animal, the "sire", the "dam", and so forth...

    Because I used outer joins, you might or might not have information for everything except the "this" alias, which you will always have. Because of the way that the relationships link, if you don't know the sire, then of course you can't know the sire's sire, dam, or anything else that depends on knowing the sire.

    At least in the example that you've shown so far, you could combine pedigree with pet. All of the data shown so far applies to a pedigree instance, which can only apply to one pet in one way. If you start to allow for multiple pedigrees (for example when you start showing internationally), the rules for pedigree registration can be different (meaning that it is possible for one animal to have two different pedigrees under two different registration systems!!). That would make a good case for keeping pet and pedigree in separate tables.

    -PatP

  9. #9
    Join Date
    Feb 2015
    Posts
    1

    copy of the template

    Quote Originally Posted by Pat Phelan View Post
    I used the alias "this" to indicate the animal to which the current row corresponds. You'll generate one row for each animal in the pedigree table using the sample I showed, and for each row there is "this" animal, the "sire", the "dam", and so forth...

    Because I used outer joins, you might or might not have information for everything except the "this" alias, which you will always have. Because of the way that the relationships link, if you don't know the sire, then of course you can't know the sire's sire, dam, or anything else that depends on knowing the sire.

    At least in the example that you've shown so far, you could combine pedigree with pet. All of the data shown so far applies to a pedigree instance, which can only apply to one pet in one way. If you start to allow for multiple pedigrees (for example when you start showing internationally), the rules for pedigree registration can be different (meaning that it is possible for one animal to have two different pedigrees under two different registration systems!!). That would make a good case for keeping pet and pedigree in separate tables.

    -PatP
    Would it be possible to have a copy of the database template you created please?

Posting Permissions

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