From: Erland Sommarskog on
David Portas (REMOVE_BEFORE_REPLYING_dportas(a)acm.org) writes:
> 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.

Your argument was:

> The fact that a column is subject to a foreign key constraint is at
> least one potential reason to avoid using null. Users and developers may
> automatically assume they can join on foreign key columns and get a
> result that includes every row in the joined tables.

But this does not get any different, if you put the optional attribute
in a table on the side. The error you describe is not with NULLs, it's
an error of people who fail to consider than an optional attribute is
exactly that. An error which is most easy to make with attributes that
are only missing for a smaller number of the instances.

And while you can sweep the NULLs under the data-model carpet, they
will pop up first thing you run an outer join.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Sylvain Lafontaine on
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CE2F3BB3E764Yazorman(a)127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas(a)acm.org) writes:
>> 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.
>
> Your argument was:
>
>> The fact that a column is subject to a foreign key constraint is at
>> least one potential reason to avoid using null. Users and developers may
>> automatically assume they can join on foreign key columns and get a
>> result that includes every row in the joined tables.
>
> But this does not get any different, if you put the optional attribute
> in a table on the side. The error you describe is not with NULLs, it's
> an error of people who fail to consider than an optional attribute is
> exactly that. An error which is most easy to make with attributes that
> are only missing for a smaller number of the instances.
>
> And while you can sweep the NULLs under the data-model carpet, they
> will pop up first thing you run an outer join.

Good point: Null is about the absence of a piece of data or the absence of
knowledge about it. However, this is also the same kind of thing with an
Outer Join: missing or absence of data. So people who don't want to have
Null values in their databases - and especially nullable foreign keys -
should also forbid themselves to use any kind of Outer Join under any
circonstance. You cannot reject something from one hand and take it back
with another.

--
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: Sylvain Lafontaine on
"David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message
news:BcSdnUVldJN9XbvWnZ2dnUVZ8tydnZ2d(a)giganews.com...

> 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. ...

Funny enough, having only one single table and the relationships between
your data materialized by the queries that you are making against it -
instead of defining it using tables and specific relationships - is the
latest trend in databases.

They are two different database platform in the Window Azure Platform and
the first one is exactly like that. As the second, called the SQL Azure
database, while externally it might ressemble an ordinary SQL-Server
database, I've heard that internally, it's simply an extension of the other
one.

The REST protocol used for accessing the Window Azure Platform is also a
direct application of that principle.

--
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
"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
news:OtXXCzNgKHA.3792(a)TK2MSFTNGP02.phx.gbl...
>
> Funny enough, having only one single table and the relationships between
> your data materialized by the queries that you are making against it -
> instead of defining it using tables and specific relationships - is the
> latest trend in databases.
>
> They are two different database platform in the Window Azure Platform and
> the first one is exactly like that. As the second, called the SQL Azure
> database, while externally it might ressemble an ordinary SQL-Server
> database, I've heard that internally, it's simply an extension of the
> other one.
>

SQL Azure is basically SQL Server running on a virtual machine. The
internals don't dictate the logical design of the database. The relational
model and for that matter the ISO SQL standard are concerned only with the
external logical interface of the DBMS, completely independently of how that
is achieved internally.

You still need to define a data model in SQL Azure and therefore as a
database designer it is still your responsibility to decide when and when
not to use nulls.

--
David Portas


From: David Portas on
"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> wrote in message
news:O$enqsNgKHA.1112(a)TK2MSFTNGP04.phx.gbl...
>
> Good point: Null is about the absence of a piece of data or the absence of
> knowledge about it. However, this is also the same kind of thing with an
> Outer Join: missing or absence of data. So people who don't want to have
> Null values in their databases - and especially nullable foreign keys -
> should also forbid themselves to use any kind of Outer Join under any
> circonstance. You cannot reject something from one hand and take it back
> with another.
>

I for one do not "reject" nulls. I merely said that it's important to
separate the use of nulls from any business concerns. The decision to use a
null or not rests entirely with the database designer and he or she cannot
and should not dodge that responsibility by claiming that the business
needed it all along.

In some SQL shops I have known it is common practice to avoid nulls in table
and view design. It is much harder to avoid them in SQL queries however
because SQL creates nulls in all sorts of places - outer joins being only
one example, SUM() of an empty set being another. The database designer
cannot always control every query that will be written against his database.
However, nulls are seldom what is truly required of any query that returns
data to a user. Developers writing reports or screens usually have to spend
time hiding or re-presenting nulls (as zeros or empty strings for example).
If SQL had better support for supplying alternative values in an outer join
then database developers would surely take advantage of that to avoid nulls
before the data got into the hands of data consumers.

--
David Portas


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8
Prev: Can SQL Server be run locally?
Next: select based on count