January 26, 2002 at 10:17 pm
Frankly, I really like his approach--learn the standards then the products and learn "right" first--and often enjoy his clear, concise writing but sometimes it's just plain over my head. I suppose this qualifies as a new topic but I have to say the whole "null" issue is a bit puzzling to me. In the language I am accustomed to, I am pretty clear about what the engine will do with the data, or lack thereof. Granted, an earlier version of the product had trouble with empty values which were the result of deletion rather than non-entry but it is now pretty darn clean in how it deals with data/non-data of the type which appears to be classified as null by SQL. It seems awkward to have nulls propagating in the dark every time you turn your back. Does the MS SQL product deal with null differently than the SQL-92 standard suggests?
Oh, I almost hate to admit it but 2 chapters later even Celko mentions getdate() as a possible alternative to current_date. Well, in only 8 more chapters I'll be able to say I'm an SQL programmer, albeit a very green one! ;^)
Sivea Key
Sivea
January 27, 2002 at 12:13 pm
NULL is pretty straight forward with a good analogy. Imagine one of those guess the marbles in the jar contents. If I take marbles from the jar, I still don't know how many marbles there are in the jar. If I add marbles, I've added marbles, but am no closer to knowing how many. The number is unknown, and no matter what operation I perform, I'm still without an answer. That's NULL.
Now where SQL Server differs from the SQL-92 standard is the evaluation of = NULL or <> NULL in a logic decision. Let's say I want to do the following:
SELECT *
FROM Orders
WHERE ShipDate = NULL
What I'm trying to do here is find all orders that haven't shipped yet. SQL Server's default behavior says I can evaluate this statement and actually get records back. SQL-92 says that any evaluation with NULL will result in NULL, no matter what. This means I can't get a true/false result and no rows will come back. What I want to do is pretty clear, but that's not the way it works under SQL-92. That's why SQL Server operates differently by default. I can go to the SQL-92 standard by issuing the command:
SET ANSI_NULLS ON
Hope this clarifies things.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 01/27/2002 12:15:14 PM
K. Brian Kelley
@kbriankelley
January 27, 2002 at 3:54 pm
Thanks, Kelly, you have set my mind at ease. It seems SQL Server behaves much the same way as FileMakerPro in regard to missing data and can be analyzed similarly. A definite relief! Is it still valid to constrain for NOT NULL whenever possible? I have not considered that an important factor prior to SQL.
Sivea
Actually, I consider it important NOT to constrain a field as not null, well, more precisely, not empty, because it prevents users from leaving a record or executing certain scripts/tasks without filling in the field. THAT can be an onerous burden for many instances of typical and practical database use. I am used to being able to search for empty or not empty and have records returned (if appropriate, of course). And to receiving errors for inapplicable data being entered, provided it matches my applied constraints or system constraints.
Which brings up a question about normalization. Most of what I understand about the 1st, 2nd,and 3rd levels of normalcy agree with what I consider good database design but I have often run into conflicts between the ideal and the realistic. Creating too many tables (or files, in FMP structure) results in many unstored values which can reduce database performance to excruciatingly slow speeds. Is this NOT an issue in SQL standard and/or Server?
Edited by - siveakey on 01/27/2002 6:08:26 PM
Edited by - siveakey on 01/27/2002 6:09:28 PM
Sivea
January 27, 2002 at 7:15 pm
There are two lines of thought on the NULL usage. One is if the value is truly not known, don't put anything in there. The other says force something, NULLs should be used as little as possible. I guess I'm too used to {0} from set theory so I'm of the first line of thought.
The bottom line, though, is that the database is an accurate model. Some fields should not be empty, even if they aren't part of a primary or candidate key. And as far as numerous tables are concerned, it's not necessarily the number of tables in the database which is a performance concern, it's if we have a lot of joins for querying. In that case we'd look at specified denormalizing decisions to try and reduce the joins without sacrificing integrity.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 27, 2002 at 9:36 pm
I had to reread your comment before I understood that we are basically agreeing with eachother. I assumed joins between the tables when I mentioned creating many of them but that wouldn't help the reader. It sounds like SQL Server behaves in much the same fashion as FileMaker Pro. Just doesn't program the same.
As long as nulls don't propogate extravagantly in SQL Server, I agree with you, leave it empty if it really is empty unless it is the primary key or some otherwise crucial data. However, I can understand why users of languages/products which propogate nulls (in the manner Celko describes) abhor a null. It's only natural. (Teehee, little physical science joke, there. I know, don't give up my day job.)
I think I'm going to like SQL Server . . .
Sivea
Sivea
January 28, 2002 at 10:45 am
Just my 2 cents.
I hate NULLs. I do allow them in places, but mostly for backwards compatibility. My personal feeling is that a value should always be known. Knowing it is empty is knowing something and NULL does not fulfill this. At best NULL indicates it is either empty OR you forgot to fill it. I prefer to use flags if something is truly empty.
The main place I get an arguement is a date value. For these we pick a default date (01-01-1900 is a popular choice) and then include a tinyint flag that indicates whether this date is being used or not.
Steve Jones
January 28, 2002 at 11:05 am
Okay, just as I was beginning to get a handle on null, Steve comes along. ;^)
I was beginning to think null in SQL was equivalent to empty in FMP. I can think of many instances when a user might only want to fill out a few pieces of data in a table and may or may not ever need to fill out the rest. An address "book" comes to mind--for some people I have a first name and a phone number, for others I might include an email address, for yet others I have names of all the family members, street address, phone number, fax, cell, and email. Oh, and maybe a website. Why would you want to force a value into the empty (as in not filled in) fields and what is the difference between empty and null in this case? It sounds to me that SQL Server will allow you to find your empty values and move them to the top or bottom of your list if you decide you want to include them or you could just filter out the empties in your display. Wouldn't you? (Just a light discussion for Monday.)
Sivea
Sivea
January 28, 2002 at 1:52 pm
That's a bit different. Null is typically used to represent an unknown value. This is different from an empty value.
For instance, I have two friends, one named James, the other named Ruth. I haven't talked with Ruth for a while and the last time I did she was considering switching jobs. If you were to ask my what job she currently held, I couldn't tell you. That fits the bill for NULL. So for a simple INSERT:
INSERT Friends
(FirstName, Job)
VALUES
('Ruth', NULL)
James, on the other hand, had a nice visit from the Prize Patrol just after the Super Bowl and is now jobless, because he quit his job. His job is not unknown, he doesn't have one and I know that. The INSERT would be:
INSERT Friends
(FirstName, Job)
VALUES
('James', '')
If the value could be unknown, I allow for NULLs. If, however, there simply isn't a value, it should be marked as empty or as some default value. A specified date, as Steve has suggested, or something of that sort that fits the data type.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 29, 2002 at 12:30 pm
Brian has a good explanation for Nulls.
BTW, for items like job, where I might not use the information for people, I tend to normalize this, so instead of having a job field in the person table, I'd have a job table that was related to the person table. Same for addresses, emails, etc.
Steve Jones
January 29, 2002 at 4:34 pm
Okay, so to have something empty you have to enter empty. Not terribly logical, from one perspective. :{ I suspect that this null factor is what was causing grief in earlier versions of FMP. I just didn't know enough basic programming theory to put the puzzle pieces together.
So, empty and unknown must be distinguished and managed in all data. No wonder 'most everyone runs around putting defaults and empties and not nulls everywhere.
That means using not null and forcing users to enter something to set a flag or to enter empty or ''. DOES '' satisfy the not null constraint? Even in number type fields?
Sivea
Edited by - siveakey on 01/29/2002 4:42:57 PM
Sivea
January 29, 2002 at 6:58 pm
It won't in the case of numeric fields. One thing that can be done is to specify a DEFAULT constraint for the given column. For instance:
CREATE TABLE Products(
SKU char(10) NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
Description varchar(100) NOT NULL,
UnitPrice money NOT NULL CONSTRAINT DF_UnitPrice DEFAULT 0,
UnitsInStock smallint NOT NULL CONSTRAINT DF_UnitsInStock DEFAULT 0)
If no value is specified on an INSERT, UnitsInStock and UnitPrice are filled with 0.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 29, 2002 at 7:52 pm
Okay. Thanks for the advice. Brian, what did you mean when you said you were comfortable with letting fields be empty. Does that mean you fill text fields with '' and numeric fields with 0? Or do you just let them not have data in them?
I can see null is going to change the way I think about database design.
Sivea
Sivea
January 29, 2002 at 8:03 pm
Having NULLs doesn't bother me all that much because of my mindset from my academic background. However, I'm not necessarily comfortable with fields going empty or filled with dummy values. I try to avoid NULLable fields, but I won't stick a dummy value in like 'N/A' unless I really mean Not Applicable.
With respect to default values, I use whatever is appropriate. In the case I gave, it makes sense to default the UnitPrice and the UnitsInStock to 0, because until we get a single unit, there's none in stock and there's no price to assign. This is a case where I'd debate a UnitPrice being NULL.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply