NULL Values and Normalization

How to Normalize for entries that might have NULL

[RandallK]

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]

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]

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]

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]

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)