Re: How to enforce uniqueness when NULL values are present?

Peter Eisentraut wrote:
> I submit that you should rethink your database schema and properly
> normalize it. You are attempting to retool the algebra that underlies
>
I don't quite understand why this is a question of normalization. As far
as I can see, my table seems to be normalized as far as possible.
> A first step in that direction would be to rethink the apparently
> troublesome use of null values.
>
Let me give you a more concrete example of the usage of my table. The
table was as follows:

Column | Type | Modifiers
--------+------------------+-----------
id | integer | not null
date | date |
value | double precision |

Let's assume that the values in this table are some limits that are
given for different data (identified by the id). Some of the limits are
only valid after a given date, whereas other limits are valid all the
time. How would you put this information into one or more tables? Of
course, I could use a special date to indicate that a limit is valid all
the time (e.g. 1970-01-01), but I don't think that this is better design
than representing this with a NULL value. Or I could split the data into
two different tables, one with the date column and one without. But then
I had to work with two tables with more or less the same meaning.
Wouldn't it be quite strange to model the same entities (the limits)
with two tables?
I know that it's always dangerous to quote the Wikipedia. Let me do it
anyway: "Attributes in tables in SQL database management systems can
optionally be designated as NULL. This indicates that the actual value
of the column is unknown _or not applicable_."
(http://en.wikipedia.org/wiki/Null_%28SQL%29) This is exactly what I
once learned about NULL values, and "not applicable" is exactly why I
use NULL values in my example.

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer