Null values. Good / Bad?

[alphacooler]

Say I want to store a user profile in a "users" table but many of the fields are optional upon signup (e.g. "your website", "DOB", etc). Is there any problem in allowing null values for optional fields? I was always told that NULL values will make your queries incredibly slow. But it would be a bit ridiculous to create more tables just to avoid NULLs.

Thanks (as you can tell, i'm somewhat new to MySQL).

 

[r937]

Is there any problem in allowing null values for optional fields?

none whatsoever

I was always told that NULL values will make your queries incredibly slow.

this is a myth




r937.com | rudy.ca

 

[shadedecho]

nulls save db space (at least in a lot of the modern rdbms's, like mysql!)... the only programming concern with nulls is that in terms of nulls being returned in certain API's results sets (such as mysql in PHP, via the mysql_fetch_row()), which will effectively (because of the language) leave blank a field in the row which was null. The implication of this is that a foreach() on the row (to get all the fields) may leave out a field in the middle of the select clause which is null for that row, and so your're field count and positions will appear off. this can be accomodated for by more careful coding, but it's a common gotcha with NULL's that i felt was worth noting, in case.

Other than this, having a NULL in a field can sometimes cause certain types of checking in SQL comparisons can be a little more complicated. For instance '' != null, so if you were trying to return all rows who have a field empty, you may have to do things like:

WHERE (field = '' OR field is null)

or other such things. So yes, there are some complications that NULL provides, but overall, the benefits (space savings) usually outweigh the extra necessary caution (which should be a programmer instinct anyway!).