Sorry for the delay in response. I had ticked the "Notify me of replies" box
but did not receive and notification.

I understand what Keith is saying but I think we are caught up in semantics.
Where I come from there is a day and it has race meetings - all with the
same date. Within each meeting there are individual races, as I'm sure we
all know. So there is a table called Meeting, and a table called Result plus
other lookup tables. I can accept the idea that date may be a dodgy field,
so the alternative is - as you suggest using a meeting ID.

So the meeting table has:
meeting_id
meetdate
trackname
distance
restrictions, etc

the result table has similar as to how you point out
there is race_id

OK, may be this is what I'm on about. I'm loading the meetings and results
in bulk. Files for each days racing.
How do I get as you have said

Field: Races_ID Autonumber, primary key
Field: RaceMeeting_ID integer type. Link it to it's namesake in the
previous table. So this is a Foreigh Key (FK)

How do I get the racemeeting_id FK to link to the correct racemeeting_id PK
in the meeting table? Or in fact, how is racemeeting_id in the meeting table
going to correctlly link to the result table, i.e. to the rows that
correspond to their respective races?




"Fred" wrote:

Your post (if you include the subject line) is really two questions:

1. Use of a Date/Time type field as a Primary or foreign key along the
general lines you discussed. The answer is probably no. Long story short,
what you "see" and enter in the field is merely a "user interface" for a
field with complicated and obscure contents


2. A question regarding a good fundamental structure of your DB. But you'd
have to give us the fundamental info (which you have not yet given us) in
order for us to try to answer that. Like what the fundamental entities are
that you want to database. If I take a guess at them, then the answer would
be what Keith has already given. But to explain that a bit more, you would
be databasing two entities, and making a table for each:

Table: RaceMeeting ( a day of races)

Field: RaceMeeting_ID Autonumber, primary key
Field: RaceMeetingDate Type: Date/Time
Fields: Other 1:1 type attributes / information about that day of races.


Table: Races

Field: Races_ID Autonumber, primary key
Field: RaceMeeting_ID integer type. Link it to it's namesake in the
previous table. So this is a Foreigh Key (FK)
Fields: Other 1:1 type attributes / information about that race. (e.g.
start time, winner etc.)