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.

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:-PatP

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~

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

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

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!

This needs to be quoted.

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