A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use of a Date Field in Primary Key



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 10:48 AM posted to microsoft.public.access.tablesdbdesign
DavidG
external usenet poster
 
Posts: 34
Default Use of a Date Field in Primary Key

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
  #2  
Old December 2nd, 2009, 12:20 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Use of a Date Field in Primary Key

"DavidG" wrote in message
...
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

  #3  
Old December 2nd, 2009, 12:20 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Use of a Date Field in Primary Key

"DavidG" wrote in message
...
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

  #4  
Old December 2nd, 2009, 12:20 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Use of a Date Field in Primary Key

"DavidG" wrote in message
...
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

  #5  
Old December 2nd, 2009, 01:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Use of a Date Field in Primary Key

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





  #6  
Old December 2nd, 2009, 01:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Use of a Date Field in Primary Key

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





  #7  
Old December 2nd, 2009, 01:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Use of a Date Field in Primary Key

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





  #8  
Old December 4th, 2009, 04:22 AM posted to microsoft.public.access.tablesdbdesign
DavidG
external usenet poster
 
Posts: 34
Default Use of a Date Field in Primary Key

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.