Welcome to WebmasterWorld Guest from 184.108.40.206
Forum Moderators: phranque
Here is a primitive example.
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?
- 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
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.
is a different Fifi to
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.