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 |
#11
|
|||
|
|||
New Database - Primary Key
On Mon, 8 Jun 2009 16:00:05 +0100, Bernard Peek
wrote: Hi Bernard, If you add an autonumber field you would call it a surrogate key. Yes, an AutoNumber can act as a surrogate key with automatically generated values. But Access refers to an autonumber field as a primary key, which I consider to be a serious flaw in its design. Not really. Access supports a table having a primary key, which is a field (or combination of fields) that uniquely identifies each record. And Access supports an AutoNumber datatype, which is a Long Integer assigned unique values automatically. However, you do not *have* to make it the primary key, although that's normally how it's used. You can have one field (or fields) as a primary key, and another non-PK AutoNumber field. So they are *not* the same thing, and Access doesn't refer to them as such. Access will offer to create a PK using an AutoNumber if you try to save a table without a PK, but that's just it trying to help. It doesn't claim that they're the same thing. So Access does not have the "serious flaw" you're referring to. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#12
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote:
Access is a very nice tool for some jobs but it has limitations. It's not suitable for applications where lots of people need frequent access to the data. For those types of applications SQL Server is more appropriate. And when the app reaches the point where SQL Server becomes viable you only need to move the data across and do some tweaking in Access and SQL Server. And keep on going using all the time and effort you've invested in Access. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Granite Fleet Manager http://www.granitefleet.com/ |
#13
|
|||
|
|||
New Database - Primary Key
In message , Armen Stein
writes On Mon, 8 Jun 2009 16:00:05 +0100, Bernard Peek wrote: Hi Bernard, If you add an autonumber field you would call it a surrogate key. Yes, an AutoNumber can act as a surrogate key with automatically generated values. But Access refers to an autonumber field as a primary key, which I consider to be a serious flaw in its design. Not really. Access supports a table having a primary key, which is a field (or combination of fields) that uniquely identifies each record. It's not that simple unfortunately. The primary key is *always* made up of attributes in the underlying entity and this does not include the autonumber field. Access should identify the data field(s) as the primary key and the autonumber field as a surrogate key. Both primary and surrogate keys uniquely identify a record and either can be used to implement joins. By merging the two ideas Access makes it more difficult than it should be to follow good practise. But that's probably an unrealistic expectation for a tool that's designed for people with no formal training in database design. -- Bernard Peek |
#14
|
|||
|
|||
New Database - Primary Key
In message , "Tony Toews
[MVP]" writes Bernard Peek wrote: Access is a very nice tool for some jobs but it has limitations. It's not suitable for applications where lots of people need frequent access to the data. For those types of applications SQL Server is more appropriate. And when the app reaches the point where SQL Server becomes viable you only need to move the data across and do some tweaking in Access and SQL Server. And keep on going using all the time and effort you've invested in Access. Indeed. Even when I have built a production database in SQL Server I am likely to use Access as a tool for database maintenance. -- Bernard Peek |
#15
|
|||
|
|||
New Database - Primary Key
Thank you all for all of your information. I really learned a lot from your
post and they cleared up a lot of questions I had about the Primary Key. Thanks Again. "Bernard Peek" wrote: In message , "Tony Toews [MVP]" writes Bernard Peek wrote: Access is a very nice tool for some jobs but it has limitations. It's not suitable for applications where lots of people need frequent access to the data. For those types of applications SQL Server is more appropriate. And when the app reaches the point where SQL Server becomes viable you only need to move the data across and do some tweaking in Access and SQL Server. And keep on going using all the time and effort you've invested in Access. Indeed. Even when I have built a production database in SQL Server I am likely to use Access as a tool for database maintenance. -- Bernard Peek |
#16
|
|||
|
|||
New Database - Primary Key
On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek
wrote: It's not that simple unfortunately. The primary key is *always* made up of attributes in the underlying entity and this does not include the autonumber field. Access should identify the data field(s) as the primary key and the autonumber field as a surrogate key. Both primary and surrogate keys uniquely identify a record and either can be used to implement joins. Hi Bernard, It seems your definition of "primary key" is narrower than mine. In Access you can choose what your primary key is. It can be a natural data field (or combination of them). Or it can be an AutoNumber. Or both, although there's no real reason to do that. The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty informative. We consistently use surrogate keys as primary keys, as we feel the advantages outweigh the disadvantages. By merging the two ideas Access makes it more difficult than it should be to follow good practise. But that's probably an unrealistic expectation for a tool that's designed for people with no formal training in database design. Well, it is designed for an easier initial learning curve. But we've been developing custom database applications using Access, while following good database design practices, for over 15 years. I have no problem with Access's implementation of AutoNumbers and primary keys. Armen Stein Microsoft Access MVP www.JStreetTech.com |
#17
|
|||
|
|||
New Database - Primary Key
In message , Armen Stein
writes On Tue, 9 Jun 2009 13:11:28 +0100, Bernard Peek wrote: It's not that simple unfortunately. The primary key is *always* made up of attributes in the underlying entity and this does not include the autonumber field. Access should identify the data field(s) as the primary key and the autonumber field as a surrogate key. Both primary and surrogate keys uniquely identify a record and either can be used to implement joins. Hi Bernard, It seems your definition of "primary key" is narrower than mine. In Access you can choose what your primary key is. It can be a natural data field (or combination of them). Or it can be an AutoNumber. Or both, although there's no real reason to do that. I've spent quite a lot of time in the comp.databases.theory newsgroup where terminology tends to be used very carefully. In fact some of the people there want to abolish the term and just use "key" so that it's clear that a table may have more than one key (even though that's generally seen as bad practise.) The writeup at http://en.wikipedia.org/wiki/Surrogate_key is pretty informative. We consistently use surrogate keys as primary keys, as we feel the advantages outweigh the disadvantages. I often use surrogate keys too, but only after I've established that there isn't a real primary key that I can use. There were once very good arguments for using surrogate keys even if a table had a real primary key, but I believe that they have mostly gone away. By merging the two ideas Access makes it more difficult than it should be to follow good practise. But that's probably an unrealistic expectation for a tool that's designed for people with no formal training in database design. Well, it is designed for an easier initial learning curve. But we've been developing custom database applications using Access, while following good database design practices, for over 15 years. I have no problem with Access's implementation of AutoNumbers and primary keys. For the benefit of those that have tuned in late to this particular debate before, many practitioners consider that using a surrogate key when a real primary key is available is bad practise. The argument is that you still need to maintain a unique index on the real data and keeping a second one on the autonumber just adds processing overhead. There's a countervailing argument that using a small field like a long-integer for creating joins is more efficient. Personally I doubt that either effect is particularly strong when considering the type of databases that are commonly built using Access. My personal view is that adding a surrogate key adds unnecessary complexity and another point of failure, so I discourage it. And as I'm the boss it's my opinion that counts. -- Bernard Peek |
#18
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote in
: The primary key is *always* made up of attributes in the underlying entity and this does not include the autonumber field. You're just restating the old natural key vs. surrogate question as though there is a right answer to the question. There isn't. You may *prefer* natural keys, but that's only a preference. No defender of natural keys has ever been able to answer the question of how you define a natural key for a table storing data about people without populating the fields with useless default values (to avoid the Null problem, i.e., PKs can't have Nulls in any of the fields). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#19
|
|||
|
|||
New Database - Primary Key
In message 00, David
W. Fenton writes Bernard Peek wrote in : The primary key is *always* made up of attributes in the underlying entity and this does not include the autonumber field. You're just restating the old natural key vs. surrogate question as though there is a right answer to the question. There isn't. You may *prefer* natural keys, but that's only a preference. No defender of natural keys has ever been able to answer the question of how you define a natural key for a table storing data about people without populating the fields with useless default values (to avoid the Null problem, i.e., PKs can't have Nulls in any of the fields). In the theory newsgroup I postulated a primary key consisting of the latitude, longitude, altitude and time of birth. I didn't claim that it was very practical. There are lots of situations where there isn't any alternative to surrogate keys, in fact the majority of tables I have come across don't have a good natural key. But in any system that I manage the DBA will have to justify the decision to use surrogates on a case-by-case basis. -- Bernard Peek |
#20
|
|||
|
|||
New Database - Primary Key
Bernard Peek wrote in
: I've spent quite a lot of time in the comp.databases.theory newsgroup They are idiots in that group. None of them really work with actual client data, so far as I can tell. Celko is one of the worst blowhards -- a smart man, but doesn't really understand the real world. Theory is a starting place for designing a real-world app. It is not the end point. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|