homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / WebmasterWorld / Webmaster General
Forum Library, Charter, Moderators: phranque

Webmaster General Forum

Relational Database Design
Ideal way to do Crossections

10+ Year Member

Msg#: 7111 posted 3:45 pm on Nov 13, 2004 (gmt 0)

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.





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?



WebmasterWorld Senior Member 10+ Year Member

Msg#: 7111 posted 3:53 pm on Nov 13, 2004 (gmt 0)

The classic, fully normalized way, is to have a third table for pet 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.


10+ Year Member

Msg#: 7111 posted 4:12 pm on Nov 13, 2004 (gmt 0)

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


10+ Year Member

Msg#: 7111 posted 4:17 pm on Nov 13, 2004 (gmt 0)

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.


10+ Year Member

Msg#: 7111 posted 4:30 pm on Nov 13, 2004 (gmt 0)

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


WebmasterWorld Senior Member 10+ Year Member

Msg#: 7111 posted 6:03 pm on Nov 13, 2004 (gmt 0)

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

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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / WebmasterWorld / Webmaster General
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved