NULLS
-
HI guys...Have a question regarding design.
When we intially create a table,all fields will allow NULLS by default, if we are uncertain whether to allow NULLS or not, in our DBA standards I see that we need to set the value to some default value[('')]. Can any one tell me the reason why they dont allow NULLS and setting the value to blank. Is there any performance issues or anything else?
-
Go back one step, did you say that all fields will allow nulls???
-
The thing is, I want to know whts wrong in allowing NULLS for a column, why do we need to set them to some default blank value istead of allowing NULLS.Is there any advantage by doing so?
-
Nothing wrong with that (unless the design is wrong, but that's another story). The only thing that might be wrong is if you want to keep the meaning of the missing value.
However I'm not an expert on tha topic, I'll let the other DBAs take this one.
-
this is my take...making an "is null" comparison is more of a pain because of all the issues related to nulls than if you were to check, say, for length of blank string which will always be zero - if you wanted to do some data manipulation or set some filters etc..
For my part I work a lot more with numeric datatypes than I do with varchars and my defaults are seldom (if ever) nulls...
**ASCII stupid question, get a stupid ANSI !!!** -
Default blank values, or NULLs, to me is generally a sign of bad design choices. Allowing NULLs as the standard seems like a bad practice.
-
Hi guys I am interested to know how do u follow in u r environment.
-
I make use of nulls where it is important to know when data is missing as oposed to blank. In such cases using blanks can cause a query to return undesired/incorrect results.
ron
-
I try to restrict my 'use' of nulls to those generated by outer joins. In schema design I generally never allow nulls.
-
Wow, what a question. This depends on what the column is used for.
I use a default of 0 when my column is of a number data type (Integer, numeric, decimal, etc). That makes doing SUMS, etc. easier.
Datetime columns, I want a default of GETDATE() or 1900-01-01, depending on the purpose of the column.
So, there isn't any 'hard and fast' rule for defaults. It has to be based on what the column is used for.
-SQLBill
-
Eh... both default blank values (like empty string in varchar) and NULLs are a sign of bad design? Well, what would you then propose to do with a column like Middle Name if the person has no middle name? I suppose you wouldn't store the word "NONE" or "N/A"... but what other options except these there are?
-
imo 'it depends'...
Bottom line is, it's a design choice. Yes, there are issues with allowing nulls where perhaps a default value serves the attribute's purpose better. Allowing null there and then could be considered a 'bad' choice. Other times a null does serve it's purpose. (eg emp_stardate, emp_enddate) For currently employed, emp_enddate could be null, or it could be 'eternity' (eg '9999-12-31') - it's the designer's chioce.
Remember that we deal with tri-logic here. (should actually be quad, but we only got one null-token to show both flavors of null) A null could mean 'don't know' or it could mean 'N/A' - subtle, but very important difference. Though we're left with only one token to show for both cases. All these things should be considered when designing the model - the more you know, the more you realize you still have to learn (at least I do
)/Kenneth
-
And everything would be much simpler if we had bi-logic (right word?), e.g. nothing other than true/false.
To answer Vladan:
PERSON { id , ... }
NAMEPART { part } (with three tuples { 'firstname' , 'middlename' , 'lastname' }
PERSONNAMEPART { personid , namepart , value }
This design would leave us with no nulls or defaults. Either a person has a middlename or he does not. It is not missing or not applicable. If we do not know his middlename then he does not have one. When we find out about it we add it, so now he has one. It is never missing in the data we store, only if we query for "people with no middlename" (or such) can we speak about people with missing middlenames.
-
Isn't that harder to handle than a null column?
-
OK, Chris, thanks for explanation. This method is fine for some occasions... but I would be afraid to use it fully /i.e. to eliminate any NULLs/ on a database with hundreds of tables (already now, when nulls are allowed in some columns), millions of rows and many users. IMHO - it would be a nightmare to create and maintain such database... And wouldn't the joining of 15 tables require more resources than joining only 3 bigger tables, even if done via views?
I think that it again boils down to the magical "it depends". There are situations where your approach is probably better, and situations where it isn't. Personally, I never had problems with nullable columns and don't consider NULLs bad practice as such - if used with care, and where it makes sense. I wouldn't try to avoid NULLs at all costs, just to minimize them to a reasonable level.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply