Relational Database Design

Ideal way to do Crossections

[Winters]

I understand how to make relational databases for the most part, and I've done it many times before (though I'm not database expert). In my current project I have a situation which I did not expect to be as difficult as it's turning out to be. Some of the items in the database can fall under more than one single category and I need to be able to query a category type and find anything that falls under that category.

Here is a primitive example.

[Owners]

OwnerID
OwnerName
OwnerAddress
...Etc

[Pets]

Owner
PetName
PetType
PetColor
...Etc

But the thing is, most pets have only one color, but some pets have more colors. Dalmatian would be black and white, for example.

Now I wanted to find a way to make a query that would return all the owners who had a white colored pet, including Dalmatians which would be white and black.

I've thought of two ways to handle this but neither of them seem like they are the ideal or most efficient way. 1) Get the data out, and then run another process on them (like regexp) to strip out the parts I need, or 2) Add in a Boolean field for each of the colors.

Can anyone tell me how this is typically done?

[victor]

The classic, fully normalized way, is to have a third table for pet color:

[pet-color]
owner-id
pet-id
color

Every pet has zero (it has no color, or the colors are as yet unknown to you) or many (it has several colors) entries in this table.

[freeflight2]

do it like victor suggested (then you could define millions of colors per pet), or - if the number of possible colors is limited:
- add n 'pet color fields' per pet, create 3 fields:
- pet_color_1
- per_color_2
- pet_color_3
(not really nice but works)

- OR -
leave it one field and store all colors in it delimited with a comma or so
pet color field may contain: color1, color2, color3, ...
(easy to handle with 'split' functions of php, perl etc.

There are also ENUMs for most databases... if you have a limited number of potential numbers you could define all possible colors first:
pet_color enum ('red', 'green', 'blue', ...) - these fields only take bits (e.g. 1 byte for 8 possible colors) which makes them very space efficient

[Winters]

Ahh, thanks Victor, that makes alot of sense, and saved me alot of headache.

One thing I don't understand. If each pet is already associated with an owner, why is OwnerID needed in the color table? I've seen other databases do this kind of thing before too, but I don't understand it. Is it just to make the query easier?

Heh, sorry I struggled a little bit with this stuff and want to make sure I have it down conceptually. Thanks again.

[freeflight2]

you are right, you don't need ownerid in the pet table.

[victor]

I was assuning (from your field names) that both the owner-name and pet-name are needed to uniquely identify a pet.

So:
Owner: Rambo
Pet: Fifi

is a different Fifi to

Owner: T800
Pet: Fifi

If all owner-id and pet-ids are unique (and that is the best way to go) then, yes, owner-id is not needed in the pet-color table.