Use of a Date Field in Primary Key

[DavidG]

Hi,
I'm building a database of horse racing information. One of the fields I'm
considering as a Key field is Date - RaceDate. One date has many races. Is
a Date field a wise choice as either a primary or part of a composite?

It seems to be a natural choice in this situation, but I've heard some
awkward stuff regarding the use of dates. Is this just hoodoo voodoo, or is
there any substance to an argument that advised against using dates?

Thanks
David G

 

[Keith Wilby]

Hi,
I'm building a database of horse racing information. One of the fields
I'm
considering as a Key field is Date - RaceDate. One date has many races.
Is
a Date field a wise choice as either a primary or part of a composite?

It seems to be a natural choice in this situation, but I've heard some
awkward stuff regarding the use of dates. Is this just hoodoo voodoo, or
is
there any substance to an argument that advised against using dates?

Thanks
David G

I don't have the time to give this a huge amount of thought but I find
myself asking what other attributes "RaceDate" would have. It would seem to
me that it is an attribute of "Race Meeting". I don't see a 1:M there.
What I see is "Race Meeting" as an entity and one of its attributes being
"meeting date". There might then be a 1:M on "race time" since each meeting
can have many races.

I'd just use numbers to link the two.

Just my 2p worth. Horses for courses.

Keith.
www.keithwilby.co.uk

 

[Fred]

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

 

[DavidG]

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?