homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

 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.



 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]!


 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!


 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. :)


 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.

ring_id, ring_name

size_id, size_desc

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.

coalesce (s.size_desc, 'No Size') as size_desc,
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