Database design and nulls

S

stoymigo

Senior Member
Joined
Dec 11, 2008
Messages
975
Reaction score
26
Would you recommend having nulls in a database or
rather a default value like 0 for integer and "" for text fields?

Reason is nulls can mess up sql joins, I just want to know whether there's a clear case for not having them or if it doesn't matter.
Thanks
 
Read up on normalization. Nulls are never good, but sometimes necessary. Defaults are definitely a better option if possible
 
I don't mind either way because my referential integrity is always rock solid. however, at the company I'm at, the general rule is NO nulls at all, even if the field is a blank, as long as it's not nulls. I always use default values wherever necessary

Remember, blanks are not the same as nulls.
 
stoymigo said:
Would you recommend having nulls in a database or
rather a default value like 0 for integer and "" for text fields?

Reason is nulls can mess up sql joins, I just want to know whether there's a clear case for not having them or if it doesn't matter.
Thanks
Click to expand...

Well it depends on what type of field it is.... if its an fk then it can't be null, if its normal data then yes.
Obviously keys and indexes can't be null

Consider this, you have a field "Address", if the data is there then it exists but if the address was not entered then you have it as null. When you want to send and email to all your customers you want to know that the address is null and not 0.
 
Nerfherder said:
Well it depends on what type of field it is.... if its an fk then it can't be null, if its normal data then yes.
Obviously keys and indexes can't be null

Consider this, you have a field "Address", if the data is there then it exists but if the address was not entered then you have it as null. When you want to send and email to all your customers you want to know that the address is null and not 0.
Click to expand...

I see but you could also check if the address is empty as in an empty space even though it's an extra function.
I guess I should bring examples to this discussion.
 
If you have a proper design and developers that know their stuff, then using nulls should not be a problem. I prefer nulls, cos that means no information exists. Joins usually occur on Primary and Foreign keys and a PK cannot be null. If the FK is null, it will not join and that is usually what you want. However if you have a proper design, you will seldom end up with FK that is null.
Also use null functions like "Is null", "Is not null" and Isnull(FieldName, Default_Value). This is for SQL Server.
 
It's purely a per-column decision and every column needs to be backed up by proper logic and constraints. You can't generalize; nulls have a critical place especially when it comes to the data entry side. Nulls don't 'mess up' joins; poor key implementation does.
 
LPCPT said:
If you have a proper design and developers that know their stuff, then using nulls should not be a problem. I prefer nulls, cos that means no information exists. Joins usually occur on Primary and Foreign keys and a PK cannot be null. If the FK is null, it will not join and that is usually what you want. However if you have a proper design, you will seldom end up with FK that is null.
Also use null functions like "Is null", "Is not null" and Isnull(FieldName, Default_Value). This is for SQL Server.
Click to expand...

+1
 
Nulls might not get indexed, depending on your database. This could be a good thing, or a bad thing, depending on your data and how you access it. Messing up sql joins is certainly very low on the list of reasons to be concerned about null values.
 
stoymigo said:
I see but you could also check if the address is empty as in an empty space even though it's an extra function.
I guess I should bring examples to this discussion.
Click to expand...

But empty and null mean two different things...

NULL has a place. Its a standardized way of saying that no data exists for this item.
 
Not to mention using null takes up less memory in the database than would a space or empty trying. Obviously a tiny bit of memory but it adds up.

I use nulls all over the place for fields that may only get data at a later point. I slit peoples throats if i see them using defaults in a field that can be perfectly substituted with a null.
 
It also very much depends on the environment you're working in, application development, BI, transactional, etc.
 
semaphore said:
How exactly does it depend on those ?:)
Click to expand...

There are various reasons why you wouldn't want nulls in certain systems. In our systems we generally replace them with a place holder such as "UNKNOWN" to avoid any joining issues

What people forget is you don't just join tables using SQL, there are various tools/applications which all handle joins and aggregate NULLS differently, such as the various ETL tools
 
LPCPT said:
If you have a proper design and developers that know their stuff, then using nulls should not be a problem. I prefer nulls, cos that means no information exists. Joins usually occur on Primary and Foreign keys and a PK cannot be null. If the FK is null, it will not join and that is usually what you want. However if you have a proper design, you will seldom end up with FK that is null.
Also use null functions like "Is null", "Is not null" and Isnull(FieldName, Default_Value). This is for SQL Server.
Click to expand...

+1

Using NULL to describe optional data forces consistency. Use NOT NULL for data that is required and not optional << consistency. Simple as that.

Using a blank space, or any other keyword to indicate 'no value' is looking for trouble. Consistency cannot be enforced an how can you be certain that the default or "empty" token you specify, isn't part of the possible sets of actual values ?
 
Agreed, (Talking about MySQL) depending on your database using NULL may not be a problem, HOWEVER, when you have huge databases with hundreds of millions or even billions of records, that NULL field may have a huge impact.
 
Trying to think of an example where you would want to have null in an int field
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X