homepage Welcome to WebmasterWorld Guest from 54.242.18.232
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
NULL Values and Normalization
How to Normalize for entries that might have NULL
RandallK




msg:4060795
 5:37 pm on Jan 14, 2010 (gmt 0)

I am working on making a database for a line of jewelry. Some of the jewelry (like rings) will have multiple sizes. Some of it will not (like a pendant).

Trying to make my database, I am going through the process of normalization. Since I will have many rings with many sizes, I need to make a separate table for 'sizes' and then have a 'Size ID' in the main product table, correct?

Except that some of the products will not have any size associated with it (like the pendant). I'm not supposed to have 'NULL' values, so how should I handle it?

Thanks in advance for any help/advice.

 

LifeinAsia




msg:4060803
 5:46 pm on Jan 14, 2010 (gmt 0)

Set SizeID=0 to mean no size associated with it.

Alternatively, you could have SizeID=1 to mean no size associated with it, then start the "real" SizeIDs from 2. (I only suggest this since some programming languages don't like 0 as an index in arrays.)

Oh, and Welcome to WebmasterWorld [webmasterworld.com]!

RandallK




msg:4060820
 5:58 pm on Jan 14, 2010 (gmt 0)

Thank you very much for the quick reply and warm welcome. I'll be using PHP and mySQL, will it have problem with 0? I'd like to use that one since A) It makes sense logically and B) My brain is used to zero being the start of arrays.

Thanks again for the assistance!

LifeinAsia




msg:4060833
 6:13 pm on Jan 14, 2010 (gmt 0)

Not sure about PHP. But it shouldn't be an issue with any DB.

I prefer A as well, but it's come back to bite me a few times. However, I still tend to do it that way and add in code to handle it on the front end. :)

Demaestro




msg:4060836
 6:15 pm on Jan 14, 2010 (gmt 0)

Instead of putting size_id on the ring table you can make up a many-to-many styled table.

tblRings
******
ring_id, ring_name

tblSizes
******
size_id, size_desc

tblRingHasSize
******
size_id, ring_id

Then to join the ring and the sizes you use the tblRingHasSize... if a ring has no size then it won't join but won't give you null... also this allows your rings to have many sizes.

select
r.ring_name,
coalesce (s.size_desc, 'No Size') as size_desc,
from
tblRing r
left join tblRingHasSize rhs on r.ring_id = rhs.ring_id
left join tblSize s on s.size_id = rhs.size_id

Keep in mind if you do it this way and a ring has multiple sizes it will return the ring as many times as it has sizes. (ie, a ring has 3 sizes this SQL will return that ring 3 times, each time with a different size)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
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