Re: date as a foreign key

> I have a master
> table Task (
> start_date date unique key,
> <parameters>
> )

it's also a bad idea to have dates as primary key (as happens here); is there really never ever going to be a chance that two tasks start on the same day? Also when your company is bought and all systems have to be merged?

> and a detail

> table TaskStatement (
> start_date date foreign key references Run.start_date,
> <etc>
> )

> I was told that it is bad idea to use date as a foreign key, but I didn't
> hear a convincing argument.

well, there's the above problem, and another one is that dates vary widely across the DBMSs, so porting is difficult.

> Use sequences instead? Well, much have been
> said about surrogate keys already. We can start 2 tasks at the same time?

> Not if lanching them manually;-)

You have a very short working day :-)

Hang on: did you say TIME ? or DATE? or TIMESTAMP or DATETIME ? Avoid those even more; I don't even think they are guaranteed to be equal to anything, only '<' and '>' need to behave as expected.

> besides, I put explicit constraint --
> unique key -- to prevent this. Am I missing anything else?

In all big systems I have seen, you sooner or later have to do interactive queries, and it's difficult to enter dates, rather than numbers or strings.

                                                                      Philip

-- 
Real programs don't eat cache (Malay)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN