default to NULL or default to 0?

searain

table product

products_id, products_name, icon_1, icon_2.

table icon

icons_id, icons_name

When I display a product, I may display some icons (0-2) with that product. So I have the above produc and icon table set up. In the product table, icon_1 and icon_2 will hold the icons_id from table icon, if there is an icon at that position.

icons_id is smallint (Auto Increment key value).

My questions are when there are no icons to display on icon_1 or icon_2, should I set the value to "NULL" or to "0".

To me it is the same. Both value will never be the icons_id of table icon, which will mean no icon will be shown.

What is your suggestion? In general, in this case, the samillint value will not be used in any calculation, only will be used to refer to another tables auto increment key. When it is EMPTY, should I set it to 0 or should I set it to NULL?

Thanks!

laserlight

It depends on your point of view - do you consider NULL to mean no value or unknown value?
If you think NULL means the field has no value, then it is appropriate to use it here.
If you think NULL means the field's value is unknown, then you should use 0 here.

searain

$sql_select_icons="SELECT icons.* FROM icons, products WHERE icons.icons_id=product.icon_1 OR icons.icons_id=product.icon_2";

When I execute this sql.

If I set icon_1 or icon_2 as 0, the database will do the matching to see if any icons record with the icons_id as 0.

If I set icon_2 or icon_2 as NULL, will the database just not doing any matching? or will it match the icons_id to see if any icons_id was set as NULL?

laserlight

No matching - you have to use IS NULL to test for nulls.

Drakla

Unless it's really necessary avoid using NULL as it adds bytes to your tables and adds extra processing.

xblue

I'd put it up the other way round, and store the relation to a product_id in the icons table. This would even allow to store more than 2 icons with each product.

products_id, products_name
icons_id, products_id, icons_name

Sxooter

Drakla wrote:
Unless it's really necessary avoid using NULL as it adds bytes to your tables and adds extra processing.

Half correct.

PostgreSQL stores nulls for a row in a single byte for the first eight null columns, and adds a byte for the next eight and so on. So, storage wise, postgreSQL is actually VERY efficient at storing NULLs.

However, the extra processing point is somewhat correct.

Remember, NULL means that a value exists, we just don't know what it is. So, if we have two rows like this:

id color size
0 red NULL
1 NULL big
2 NULL medium
3 blue NULL

Rows 0 and 3 do NOT necessarily have the same size and 1 and 2 do NOT have the same color. They might, but we just don't know.

Now, for something that doesn't apply, you should use something that conveys that, not NULL. Use a different key, like NA. If there is no color or size for an item in that table, then it would make sense that they would "match" on that, since the absence of a value is not the same as a value that is unknown.

Note that you shouldn't "avoid" nulls anymore than you should try to use them a lot. You should simply use them where it's appropriate, and remember that NULL is NOT a value, but an unknown, possibly unknowable value.

learn coalesce and nullif, both pretty standard functions in dbland.

csn

Extra processing? CPU or getting the data how you want it (e.g. coalesce, nullif, etc.)? I've never noticed any performance impact when using NULL's - they're indexed along with other values. And they definitely have their place in db design - never use 0's instead!

Sxooter

Actually, in PostgreSQL., while NULLS are indexed, the indexes often can't be used, because there isn't a built in operator for the index and "equality" of nulls. I.e.

create table test (id int primary key, enum id, info text);
create index test_enum_dx on test(enum);
<load test with 100,000 rows or something like that,
many with nulls for enum>
analyze;
select * from test where enum IS NULL will not use the index (at least in 7.4 and before) because there are no natural btree operators for "IS NULL". You have to create a sepearate index:

create index test_enumisnull_dx on test (enum) where enum IS NULL;

Then you get an index if the % of the field that is null makes sense for it.