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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match