Re: date as a foreign key

Just a general comment on the use of timestamps. This comment may or may not apply to the originator of this thread.

Real Numbers (floating point numbers) are a poor choice for indexes, and therefore a poor choice for primary keys. Although Oracle timestamps are not real numbers, they suffer from the same problem somewhat. The problem is that we tend to treat "equality" of two measurements as being a somewhat fuzzy concept. If the difference between two measurements is less than some suitably small value, epsilon, we say "close enough" and regard them as equal.

But a primary key, foreign key match isn't satisfied by "close enough" . The test is for exact equality. Now, is time, in Oracle, something you measure or something you count? Strictly speaking Oracle counts time in centisecond beats. But if the epsilon is larger than a centisecond, you could be in trouble. So I would avoid using timestamps as primary keys in Oracle databases.

None of the above applies to integers. Nor does it apply to DATES as such. If you use timestamps to represent dates, and set the time to midnight all the time, you might as well think of the date as an integer.

--
Regards,
    David Cressey
    www.dcressey.com
"DODO" <dodo_at_Ihatespam.com> wrote in message
news:Q%Hr7.31249$Z2.442875_at_nnrp1.uunet.ca...


> Hello,


>

> I cannot see the whole thread, so I'm probably missing things already

said.


> Just my 0.2 cents: I know a couple of guys who set a time stamp as primary


> key. When starting testing their app, they got lots of integrity

constraint


> messages from Oracle. Basically their test program was faster in inserts


> than the machine's clock. They were very proud of their "fix": let's put

> some sleep statements in the code.

>

> "Philip Lijnzaad" <lijnzaad_at_ebi.ac.uk> wrote in message

> news:u7u1xyw1is.fsf_at_sol6.ebi.ac.uk...

> >

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

> >

>

>