Database design and nulls

While nulls suck, reality dictates that you learn to deal with them. Business processes demand that.
 
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...

Maybe I'm missing something, but I don't understand why this would mess up joins. For example if a join is table1.column1 = table2.column1, then clearly in :

1. If table1.column1 is null and table2.column1 contains ant value other than null, they are not equal.
2. Visa versa re 1 above, not equal
3. If both are null, then again. Not equal. This is industry standard SQL

It's up to the developer to decide to test for nulls:
table1.column1 is null AND table2.column1 is null

Or : nvl(table1.column1,'XXX') = nvl(table2.column2,'XXX') ofc you need to be careful that 'XXX' can't be an normal value. Usually only use this where the column is meant to to store a simple Y(yes), N(no) and you want SQL to assume nulls are N. Whether this is good design or bad is not the point. The point is it can exist and you need to know how to deal with it.

Null values are actually very common. In some cases they are desired. For example if you have column which is a varchar2(200), why fill it with defaults? Some conventions say that you only put stuff in character columns that actually means something.

You can argue that its you shouldnt have nulls in columns of integer or number. It's normal and fine in text fields. For example, if you have a seperate column for a persons middle names, not everyone has a middle name. Putting a default in this column is just plain stupid. Blanks... Just as stupid. A null conveys the correct information. They don't have a middle name. Don't give them one. :)

Yes, tedious, but not a major issue

Just remember what a null is meant to convey. It means the column has no value in it. That in itself is not incorrect. If you put a default in a column, then that default is a real value. It can be misleading putting values into columns which users do not know about.

It's all about doing the appropriate thing for that bit of data.
 
xrapidx said:
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
Click to expand...

Your ETL tools suck then. The ones we've used handle them perfectly fine.
 
zippy said:
Maybe I'm missing something, but I don't understand why this would mess up joins. For example if a join is table1.column1 = table2.column1, then clearly in :

1. If table1.column1 is null and table2.column1 contains ant value other than null, they are not equal.
2. Visa versa re 1 above, not equal
3. If both are null, then again. Not equal. This is industry standard SQL

It's up to the developer to decide to test for nulls:
table1.column1 is null AND table2.column1 is null

Or : nvl(table1.column1,'XXX') = nvl(table2.column2,'XXX') ofc you need to be careful that 'XXX' can't be an normal value. Usually only use this where the column is meant to to store a simple Y(yes), N(no) and you want SQL to assume nulls are N. Whether this is good design or bad is not the point. The point is it can exist and you need to know how to deal with it.

Null values are actually very common. In some cases they are desired. For example if you have column which is a varchar2(200), why fill it with defaults? Some conventions say that you only put stuff in character columns that actually means something.

You can argue that its you shouldnt have nulls in columns of integer or number. It's normal and fine in text fields. For example, if you have a seperate column for a persons middle names, not everyone has a middle name. Putting a default in this column is just plain stupid. Blanks... Just as stupid. A null conveys the correct information. They don't have a middle name. Don't give them one. :)

Yes, tedious, but not a major issue

Just remember what a null is meant to convey. It means the column has no value in it. That in itself is not incorrect. If you put a default in a column, then that default is a real value. It can be misleading putting values into columns which users do not know about.

It's all about doing the appropriate thing for that bit of data.
Click to expand...

Good post because I can demonstrate an example.
Let's say your query contained this:

table1.column1 + table1.column2 = table2.column1 + table2.column2
Click to expand...
This is appending two columns

there are two expressions above, if one field is null in an expression it wll become null eg:
"Firstname" + Null = "Firstname" + ""
--this becomes Null = "Firstname "
Click to expand...

The above is not the same, although when you have this query you would want it to be the same.
If there were no null fields then this would be avoidable, but I know the IsNull function solves this problem.

Let me know regarding that if that is normal practise, but other than that I'll have to accept that nulls have their place...and I don't know much about this topic but I'm studying database design so I'm learning.
And nulls don't have a major impact on the business, but can cause some hiccups if you don't know your sql.
 
The 'no nulls' rule is total bull****. Nulls are actually quite important. Like another poster said, null means no data for that field yet. There is a big difference between null and some default value. If nulls are giving you problems with joins, then you need to go back to SQL school
 
@Stormigo

If a dev can't deal with nulls in database cols, then he/she isn't a dev?

When the table is created, you specify not null constraint for columns that shouldn't have nulls. If the table column doesnt have the not null constraint then nulls are okay. And putting crap in like "UNKNOWN"........, seriously don't put garbage in your database.....

Somebody is going to put in "Unknown" and "UNKNOWN" != "Unknown"

Hire a real dev, not a designer :) /ducks
 
The absence of a value shouldn't be used as a value.

It's not just good database design, but also true in auditing. If, for example, you had a form where a user is supposed to accept terms and conditions, and your column depicting the capture of this acceptance is null, does it mean he didn't accept it? Or, was he never presented with the choice?
 
grimstoner said:
The absence of a value shouldn't be used as a value.

It's not just good database design, but also true in auditing. If, for example, you had a form where a user is supposed to accept terms and conditions, and your column depicting the capture of this acceptance is null, does it mean he didn't accept it? Or, was he never presented with the choice?
Click to expand...

Said it before. Null columns have a place. You use it where it is appropriate. We can bring down mybb's database if we are going to list every situation where it's isn't appropriate or where it isn't . If a developer can't decide when to use nulls or when not, then stay away from the database design and programming. Rather make pretty pictures for people to put in their websites or stick advising on colour schemes or something :)
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X