Reuse primary or create secondary tables?

[squirrly]

I have a local community lost and found pet website I’m rebuilding. Following bits of this database model -- http://www.databaseanswers.org/data_...pets/index.htm, and requiring primary and secondary breed and color choices as shown on this website -- http://www.lostandpound.com/pet_data.php?s=lost, would I reuse the same tables or create secondary tables?

Also, how is it best to handle “none, unspecified, other”? Include them in the table with null values, specific values, or leave them out of the tables and include them as static options with null values in a dynamic dropdown list?

Sorry for my lack of experience at this point, but I appreciate any guidance to help get me through this pitstop. Here’s an example:

PETS
PetID (PK)
StatusID (FK)
TypeID (FK)
BreedID (FK)
ColorID (FK)
Breed2ID?? (FK)
Color2ID?? (FK)

STATUS
StatusID (PK)
Status [lost, found]

TYPES
TypeID (PK)
Type [cat, dog, bird]

BREEDS
BreedID (PK)
Breed [all cat/dog/bird breeds]
TypeID (FK)

COLOR
ColorID (PK)
Color


SECONDARY TABLES ???

BREEDS2
Breed2ID (PK)
Breed
TypeID (FK)

COLOR2


Color2ID (PK)
Color

[jzwp11]

No, you would not have secondary tables as you describe. If a pet can be of multiple breeds and a breed can apply to may pets, then that describes a many-to-many relationship which is typically handled with a junction table. The same would be true for the colors. The first step is to remove the breed and color foreign keys from the pet table

PETS
PetID (PK)
StatusID (FK)
TypeID (FK)

Now we need the breed junction table

PETBREEDS
-pkPetBreedID primary key,autonumber
-PETID (FK to PETS table)
-BREEDID (FK to BREED table)

You would use the same approach for the colors

PETCOLORS
-pkPetColoerID primary key, autonumber
-PETID (FK to PETS table)
-COLORID (FK to COLOR table)

[squirrly]

Aha! I get it... I've been working solely with so many one-to-many relationships, I didn't even think beyond that. THANK YOU! I just needed the nudge. I now understand why these sites have people choose just one -- main breed, main color. It simplifies things.

As for as handling "unspecified" or "other" in a table with no value, should I start a new discussion on that? I'm guessing if the value is null, it really doesn't matter how I handle it. Regardless, I'll have to code my SQL to include those items in any query, so when people search for their lost Pug, it brings up Pugs and unspecifieds, so as not to miss a possible match.

[jzwp11]

As for as handling "unspecified" or "other" in a table with no value, should I start a new discussion on that? I'm guessing if the value is null, it really doesn't matter how I handle it. Regardless, I'll have to code my SQL to include those items in any query, so when people search for their lost Pug, it brings up Pugs and unspecifieds, so as not to miss a possible match.

If you go with the field rather than the junction table approach and allow the user not to enter a value in the field or a record in the junction table, you would have to account for that in your queries. The other option is to force the user to select something even if the only choice is not specified/other.

[squirrly]

Thanks, I'll give that some thought. I'll also refresh myself on the basics of relationships. ;o)

[jzwp11]

Feel free to post back with any other design questions.

[squirrly]

Hi, revisiting this for a moment...

If a junction table is used for colors as shown above, and I want the output to list those colors in a specific order (order of dominance, for instance), how would that be done?
For example, I want a pet listed as "black/tan", not "tan/black".

[jzwp11]

If the order of the colors is not dependent on the pet, then you could add a sequence number field to the colors table

COLOR
ColorID (PK)
Color
seqNo

If the order of the colors is dependent upon the particular pet, then you would need the sequence number field in the junction table.

PETCOLORS
-pkPetColoerID primary key, autonumber
-PETID (FK to PETS table)
-COLORID (FK to COLOR table)
-seqNo

Now, if a pet has multiple colors then you would have multiple records in the junction table for that pet. To combine those multiple records into 1 output string such as black/tan, you would need to concatenate the multiple colors. To do that you need a custom function. Allen Browne has one on his site here.