Re: date as a foreign key

> Sorry, I missed important detail: DATE above is Oracle date, which is
> essentially a timestamp.

OK.

> OK. However, this is essentially equivalent to saying that no user-defined
> domain type is desired in the RDBMS, as it can't be ported.

(SQL standard defines DATE, DATETIME, TIME and TIMESTAMP, but in practice,  they're not. That Oracle treats DATE and DATETIME as synonymous is wrong, and  annoying)

>> Avoid those
>> even more; I don't even think they are guaranteed to be equal to anything,
>> only '<' and '>' need to behave as expected. 

> Same as "real" numbers? Isn't there a way to construct 2 equal TIMESTAMP
> values (NUMBERs, etc) for which equality guaranteed to return "TRUE"?

I'm not sure; this should be documented by Oracle. The only thing you have to be careful about is that you obtain the timestamp just once, and use that in all your INSERT statements (this should be obvious, but I'm just saying it because some people might be tempted to use SYSDATE in a few places, getting different (nano?micro?milli?)seconds back)

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

> Well, it seems that the tradeoff is meaningless sequence number versus
> somewhat less convenient date.

that's true ... I think in your case I would go for a Unix timestamp (number of seconds since 1970-01-01 00:00:00 ... 32 bit quantity, roles over in 2038, but by that time, systems are expected to all be 64 bits). This way it is both a timestamp, but has the advantage that it doesn't look like one.

So, all in all, I now agree that in the circumstances you sketched, it might be acceptable (or even useful) to use timestamp-like things instead of sequences. Comments anyone else ?

                                                                      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