homepage Welcome to WebmasterWorld Guest from 54.198.46.115
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / WebmasterWorld / Webmaster General
Forum Library, Charter, Moderators: phranque & physics

Webmaster General Forum

    
Relational Database Design
Ideal way to do Crossections
Winters




msg:389889
 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.

[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




msg:389890
 3:53 pm on Nov 13, 2004 (gmt 0)

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




msg:389891
 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

Winters




msg:389892
 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.

freeflight2




msg:389893
 4:30 pm on Nov 13, 2004 (gmt 0)

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

victor




msg:389894
 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.

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.

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