I am currently setting out some best practice guidelines for DS EE developers in our department and want to canvas opinions as to if it is possible or advisable to set down a firm rule for nullable columns.
We have two schools of thought. One camp maintains that nullable columns cause too many problems, e.g. in transform derivations, writing to sequential files etc and should be converted to non-nullable and a suitable value inserted to signify a Null as soon as possible in the data stream.
The other camp points out that it is not always possible to choose a suitable value (for example a bank account balance), that Null sometimes has a legitimate 'meaning' within the data and every data type conversion incurs a (small) performance penalty, they insist that if a column starts nullable it should remain nullable throughout, especially as it will probably be written to a nullable output column at the end of the ETL process, and transforms file, writes etc should be coded to handle nulls properly.
I am in the second camp, but I can see the legitimacy of the other viewpoint. What do you do at your site? Is there a standard or do you let developers choose on a case by case basis?
Not an interview question, I promise.
Nulls - to convert or not to convert?
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 72
- Joined: Thu Sep 04, 2003 5:01 am
- Location: UK & Europe
I prefer it when a column is not nullable; if there is a choice then I always recommend not allowing null values. This simplifies database queries and storage.
But as you have pointed out, there are many legitimate cases where a null value should be used. Trying to avoid a null in those columns is a mistake (I shudder to remembering the issues I've seen with "special" values in fields that are meant to denote a null or unknown value - what a mess!).
I think that there is no real choice when it comes to nullable fields; the metadata or business rules will determine nullability.
But as you have pointed out, there are many legitimate cases where a null value should be used. Trying to avoid a null in those columns is a mistake (I shudder to remembering the issues I've seen with "special" values in fields that are meant to denote a null or unknown value - what a mess!).
I think that there is no real choice when it comes to nullable fields; the metadata or business rules will determine nullability.
-
- Premium Member
- Posts: 72
- Joined: Thu Sep 04, 2003 5:01 am
- Location: UK & Europe
-
- Premium Member
- Posts: 72
- Joined: Thu Sep 04, 2003 5:01 am
- Location: UK & Europe
... opinions as to if it is possible or advisable to set down a firm rule for nullable columns.
I guess the conclusion is no, it is not advisable to lay down a one-size-fits-all-occasions null conversion rule.
.. comes from a Harry Potter book
That would explain Matt's beard ... he fancies himself as the next Dumbledore ...
I guess the conclusion is no, it is not advisable to lay down a one-size-fits-all-occasions null conversion rule.
.. comes from a Harry Potter book
That would explain Matt's beard ... he fancies himself as the next Dumbledore ...
Dumbledore
I believe he becomes a null value...