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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Any issues with primary key as date/time column
Are there any issues in having a date/time field as my primary key? thanks.
|
#2
|
|||
|
|||
Any issues with primary key as date/time column
Philosophically, provided there's no way you could have two (or more)
records with identical values in that field, I don't see why not. Practically, or from a database standpoint, I don't know. Have you tried searching on-line? Good luck Regards Jeff Boyce Microsoft Office/Access MVP "mscertified" wrote in message ... Are there any issues in having a date/time field as my primary key? thanks. |
#3
|
|||
|
|||
Any issues with primary key as date/time column
How many users entering records at the same time?
Will you be importing data into that table? Could you ever be inserting records after midnight? If it's only one or a few users there is little chance of inserting that two or more new records at the same second. If you import data into the table, you'll have to make sure that the data has a date/time field without duplicate records. During the Daylight Savings Time change in the Fall, you could possibly have the same date/time stamp for different records as the time falls back an hour. Other than that, it shouldn't be a problem. You could set the default value for any new records to Now(). Personally I perfer autonumbers for PKs as they should be unique compared to any other record in the table and don't have a 'meaning'. Date/Time just might and someone may say that the record should have been entered yesterday so change it. Not good. On the other hand if the database is used by many people, it could be a problem. Consider if EBay or Amazon would make the date/time a primary key for one of their bigger tables. They couldn't add more than 60 sales a minute for example. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "mscertified" wrote: Are there any issues in having a date/time field as my primary key? thanks. |
#4
|
|||
|
|||
Any issues with primary key as date/time column
This is normally not a good idea.
Discussion follows, but the main thing, speaking as the voice of experience, is that it is possible that later you will want to use an append query with multiple records as a data source, and all the Date/Time values will be identical, forcing you to jump though hoops to enter the records. Anyway, more theoretical objections: In a JET/MDB database, date/time is stored as a real (floating point) number. Floating point numbers are not a good choice of primary key because they are not exact. You may have two floating point numbers that are the same, but different. Or you may have two floating point numbers that are different, but the same. If you are only using the DATE part of the date/time, this is less of an issue. Floating point numbers still make inefficient keys: there is a lot of overhead in handling floating point numbers, but at least they won't accidentally clash or get lost. I have to say that in real life, with a JET/MDB database, using real dates and times, you probably won't see this as a problem, with the current version of JET. Still, that doesn't make it a good idea. Also, as a secondary consideration, Date/Time and floating point numbers are a really bad choice for use with SQL Server. SQL Server Date/Times are stored differently, so none of your Date/Times would match exactly, and floating point numbers don't always translate exactly either, so you couldn't fake it that way either. Again, you might not see this problem until you add the automated interface or web interface, and it tries to add multiple records at the same time, but for Access/SQL Server it is just a really bad idea anyway, and you want to avoid it. (david) "mscertified" wrote in message ... Are there any issues in having a date/time field as my primary key? thanks. |
#5
|
|||
|
|||
Any issues with primary key as date/time column
"David Epsom Dot Com Dot au" david@epsomdotcom wrote:
In a JET/MDB database, date/time is stored as a real (floating point) number. Agreed with the snipped explanation. You did a better job of describing those issues than I would've. Also what if there are child tables with this table? shudder Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ |
Thread Tools | |
Display Modes | |
|
|