Welcome to WebmasterWorld Guest from 54.146.248.111

Forum Moderators: open

Message Too Old, No Replies

NULL Values and Normalization

How to Normalize for entries that might have NULL

     

RandallK

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

5+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

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

5+ Year Member



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

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

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

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

WebmasterWorld Senior Member demaestro is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

 

Featured Threads

Hot Threads This Week

Hot Threads This Month