Nullable Foreign key constriants
in [Programming SQL]
|
From: David Portas on 14 Dec 2009 15:15 "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:eL6vnRPfKHA.1652(a)TK2MSFTNGP05.phx.gbl... > > Instead of making it simpler for him, you get exactly the opposite: the > true nature of the relationship is buried under a cover of bogus data and > other magic keys and the final query is more complex to read and write; > all this to get the exact same result at the end. I don't see what's the > advantage here. > I never suggested using "bogus" data. If the fact represented by a row does not have some particular attribute then it should be represented in a table that does not have that attribute. Nulls (like denormalization) are always something *added* to a data model, not something to be taken away or "buried". I always teach people who are learning database design to start out by designing models without nulls. I think that's a good discipline to get into the habit of. A more experienced designer will pick when and when not to use a null but should always be aware that she has a choice and that it's the designer's decision about whether and when it's advantageous to use a null or not. That was the main point I wanted to make in this thread. -- David Portas
From: Jay on 14 Dec 2009 15:22 And this is a problem because? Seriously, I don't understand your objection. "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:dvKdnQosXaHFB7vWnZ2dnUVZ7qqdnZ2d(a)giganews.com... > "Jay" <spam(a)nospam.org> wrote in message > news:%23BP4THPfKHA.2780(a)TK2MSFTNGP05.phx.gbl... >>I don't think that's right, but am more than willing to be proven wrong. >>Here is a small stub, please modify it to show what you're talking about. >> > > I don't see why it needs modifying. Your example has every row in the > referencing table ("try") joining to exactly one row in the parent > ("lookup") - which is what I said. > > -- > David Portas > >
From: Sylvain Lafontaine on 14 Dec 2009 16:58 "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:WaudnW99oOFlAbvWnZ2dnUVZ8smdnZ2d(a)giganews.com... > I always teach people who are learning database design to start out by > designing models without nulls. I think that's a good discipline to get > into the habit of. A more experienced designer will pick when and when not > to use a null but should always be aware that she has a choice and that > it's the designer's decision about whether and when it's advantageous to > use a null or not. That was the main point I wanted to make in this > thread. > > -- > David Portas � -- I always teach people who are learning database design to start out by designing models without nulls. I think that's a good discipline to get into the habit of. � And you are basing this on what ground? Saying that you don't like Nulls is not sufficient, you most argue in favor of your point by putting forward some good arguments against their use but the only reason that I've seen so far - and not only just in this thread but in all others - can usually be resumed as � -- I don't like Null and I prefer to use a more complicated design. �. This looks to me more like a statement of belief - a religion - than anything else. Database design should be a science, not a religion. It took many milleniums for the Old Mathematicians before discovering the concept of Zero. It looks like that some people here are repeating the same process. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French)
From: David Portas on 14 Dec 2009 17:49 "Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message news:unYRQiQfKHA.6096(a)TK2MSFTNGP02.phx.gbl... > > � -- I always teach people who are learning database design to start out > by designing models without nulls. I think that's a good discipline to get > into the habit of. � > > And you are basing this on what ground? Saying that you don't like Nulls > is not sufficient, you most argue in favor of your point by putting > forward some good arguments against their use but the only reason that > I've seen so far - and not only just in this thread but in all others - > can usually be resumed as � -- I don't like Null and I prefer to use a > more complicated design. �. This looks to me more like a statement of > belief - a religion - than anything else. Database design should be a > science, not a religion. > My reasons are that all information can be accurately represented without nulls and that the behaviour of nulls in SQL does not accurately reflect reality. Nulls therefore complicate the data model with results that are incorrect in reality. So I think it's reasonable and pragmatic to say that you should only use a null where it has some advantage great enough to offset that disadvantage. When you say that the design without nulls is "more complicated" I understand you to mean that by making a column nullable you can "simplify" the design by removing some other column or table. That alone is not a very sufficient argument however. If reducing the number of tables and columns was in itself a reason to use null then the conclusion would be to have just one table in every database and make every non-key column in that table nullable. I'm sure you don't advocate that. Therefore it is perfectly sensible to have some guideline as to when to use nulls and when not. Science (and a bit of art) not religion. > It took many milleniums for the Old Mathematicians before discovering the > concept of Zero. It looks like that some people here are repeating the > same process. SQL is really not that important! Null is just a peculiar feature of a humble computer language (and it is a notoriously deficient language after all!). Mathematicians, scientists and the man in the street (as well as a great many database designers) continue to describe the world without using null just as they have done for millennia. They will do so long after SQL has been forgotten. -- David Portas
From: Jay on 14 Dec 2009 18:58
> I don't see why it needs modifying. Your example has every row in the > referencing table ("try") joining to exactly one row in the parent > ("lookup") - which is what I said. I fail to see why this is anything but expected and desired behavior. And I repeat my challenge for you to use the stub I provided and show us why it is bad. With data. |