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

On Saturday 10 March 2007, Christian Schröder wrote:
> 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?

- SNIP -

> 1. If a record with a given id and a null value in the date field
> exists, no other record with the same id is allowed.
> 2. If multiple records with the same id exist, they must have
> different values in the date field and none of them must have a
> null value in this field.
Seems to me that this is what you are looking for:

TABLE listofids:

     Column |       Type       | Modifiers
    --------+------------------+-----------
     id     | integer          | not null
hasdates | bool | default null
unique(id, hasdates)

TABLE listofidsdates:
     Column |       Type       | Modifiers
    --------+------------------+-----------
     listofids_id     | integer          | not null REFERENCES listofids(id)
     date   | date             | not null
unique(listofids_id, date)

When there are dates, set listofids.hasdates=null. Otherwise, set it to true.
Does this seem most properly normalized? (it's how I would do it!) How could
this be done better?

-Ben