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 |
#41
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
And memo fields
cannot be used as indexes or searched on I don't know what you mean but memo fields can be indexed and can be used in WHERE and HAVING clauses; you can cast them using CSTR() in SQL code to prevent them from being truncated in certain circumstances. Then you're a better man that I, because I could never get them to work for me in that regard. But that wasn't the first time I've said that, and it CERTAINLY won't be the last - heh.... Dennis |
#42
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
M.
I always love these discussions, because you have some purists, some "religious fanatics", and some simpletons; call me a simpleton. The reason I use "meaningless" primary keys is that it saves me work!!! When I want to update a record, or select a record with an autonumber PK, I type: WHERE ID = 123 If I use a composite PK, I have to type: WHERE [somefield] = somevalue AND [someotherfield] = somevalue AND [athirdfield] = somevalue AND .... This seems like a lot of extra work to me, so I avoid it where I can. It also occurs to me that if you want to use the composite key, then if you want to use this stuff in a list or combo box, and want to be able to select the specific record, then you need to include all of the fields necessary for the PK (which increases the load on your network). Just my 2 cents! Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "M." wrote: Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#43
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
I'm not sure why I'm jumping in here and I'm not sure if someone has already
said what I am going to say so I apologize in advance. I also have over 30 years of database design experience and in my mainframe days, all primary keys were "natural" and many were compound. It took me a few years of using Access to become converted to the "autonumber as PK camp" but I have. I find autonumbers extremely easy to work with. Access does have an issue with autonumbers that are not defined as primary keys so I would NEVER use a "natural" PK in a table that also had an autonumber. the autonumber would ALWAYS be the PK and would be used for all relationships. When I have business rules to satisfy that revolve around uniqueness of "natural" keys, I use unique indexes. The benefits of using an autonumber PK a 1. there is never a need to cascade PK updates since the PK is an autonumber and by definition cannot be changed. 2. Business rules regarding uniqueness can be implemented with unique indexes. 3. In the cases where one of the "natural" keys may validly be blank, I don't have an issue with a unique index whereas I would have an issue if I defined a compound primary key since PKs may not contain null values. SSN is a good example here (aside from the fact that there are laws prohibiting its use as a PK) since not everyone will have one (h1b visa holders for one) and there are only limited cases where a person actually has to provide one to the requestor. 4. Combo/List boxes require code behind the scenes to work if the unique identifier is compound. 5. As the hierarchy gets deeper, the compound key grows and joins become more unwieldy. It is way too easy to omit one field out of 5 in a join with disastrous results if the error isn't discovered quickly. The only pro for using autonumber PKs is that a lot of your tables will end up with lots of numeric columns that you would prefer to view as text when you open the table. The solution is NOT table level lookups, it is creating queries that join the tables so you can see the identifying "natural" key data from the parent table. You only have to create one of these views for each table so it's not like it is a great deal of work. Then for yourself (because ONLY you should be viewing tables or queries directly), just remember to open the query rather than the table when you want to see the natural key values. That's my opinion and I'm stickin' to it "M." wrote in message ... Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#44
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Dear Dale,
You could still use your preference for the (unique) autonumber field in the WHERE statement (as suggested for defining relations in my example), even if it's not the primary key. The same goes for the lookup box example. I'm in the mean time convinced that it's a matter of taste (and maybe of performance for the Jet engine) to decide between an artificial primary key and a real data primary key. What still interests me, however, is how pepole prevent entry of duplicate records when they use a autonumber field as primary index. This choice doesn't offer you any guarantee that the real data in your record is accidentally entered a second time in a later record. In my opinion you would still need a unique single (e.g. ISBN number) or multifield index (e.g. book title + author name) to prevent duplicate entry. In the end, it's then a matter of taste whether you name the autonumber field index primary or the composite field(s) index primary. If you disagree, please let me know, regards, M. "Dale Fye" wrote: M. I always love these discussions, because you have some purists, some "religious fanatics", and some simpletons; call me a simpleton. The reason I use "meaningless" primary keys is that it saves me work!!! When I want to update a record, or select a record with an autonumber PK, I type: WHERE ID = 123 If I use a composite PK, I have to type: WHERE [somefield] = somevalue AND [someotherfield] = somevalue AND [athirdfield] = somevalue AND .... This seems like a lot of extra work to me, so I avoid it where I can. It also occurs to me that if you want to use the composite key, then if you want to use this stuff in a list or combo box, and want to be able to select the specific record, then you need to include all of the fields necessary for the PK (which increases the load on your network). Just my 2 cents! Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "M." wrote: Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#45
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Dale Fye wrote:
I always love these discussions, because you have some purists, some "religious fanatics", and some simpletons; call me a simpleton. The reason I use "meaningless" primary keys is that it saves me work!!! chuckle Excellent comment. When I want to update a record, or select a record with an autonumber PK, I type: WHERE ID = 123 If I use a composite PK, I have to type: WHERE [somefield] = somevalue AND [someotherfield] = somevalue AND [athirdfield] = somevalue AND .... Consider if you have a parent and subform based a composite PK? That's a lot of extra work there figuring out what fields go where. 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/ |
#46
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
I am certain that there are some performance issues associated with either
decision. I generally use the Autonumber field as my PK, but use multi-field unique indexes, and application level business logic, to prevent "duplicate entries". As long as you restrict your users access to the raw data, it is frequently easier to implement the business logic in the application rather then through table constraints. Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "M." wrote: Dear Dale, You could still use your preference for the (unique) autonumber field in the WHERE statement (as suggested for defining relations in my example), even if it's not the primary key. The same goes for the lookup box example. I'm in the mean time convinced that it's a matter of taste (and maybe of performance for the Jet engine) to decide between an artificial primary key and a real data primary key. What still interests me, however, is how pepole prevent entry of duplicate records when they use a autonumber field as primary index. This choice doesn't offer you any guarantee that the real data in your record is accidentally entered a second time in a later record. In my opinion you would still need a unique single (e.g. ISBN number) or multifield index (e.g. book title + author name) to prevent duplicate entry. In the end, it's then a matter of taste whether you name the autonumber field index primary or the composite field(s) index primary. If you disagree, please let me know, regards, M. "Dale Fye" wrote: M. I always love these discussions, because you have some purists, some "religious fanatics", and some simpletons; call me a simpleton. The reason I use "meaningless" primary keys is that it saves me work!!! When I want to update a record, or select a record with an autonumber PK, I type: WHERE ID = 123 If I use a composite PK, I have to type: WHERE [somefield] = somevalue AND [someotherfield] = somevalue AND [athirdfield] = somevalue AND .... This seems like a lot of extra work to me, so I avoid it where I can. It also occurs to me that if you want to use the composite key, then if you want to use this stuff in a list or combo box, and want to be able to select the specific record, then you need to include all of the fields necessary for the PK (which increases the load on your network). Just my 2 cents! Dale -- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "M." wrote: Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#47
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Amen, brother!
-- Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "Pat Hartman" wrote: I'm not sure why I'm jumping in here and I'm not sure if someone has already said what I am going to say so I apologize in advance. I also have over 30 years of database design experience and in my mainframe days, all primary keys were "natural" and many were compound. It took me a few years of using Access to become converted to the "autonumber as PK camp" but I have. I find autonumbers extremely easy to work with. Access does have an issue with autonumbers that are not defined as primary keys so I would NEVER use a "natural" PK in a table that also had an autonumber. the autonumber would ALWAYS be the PK and would be used for all relationships. When I have business rules to satisfy that revolve around uniqueness of "natural" keys, I use unique indexes. The benefits of using an autonumber PK a 1. there is never a need to cascade PK updates since the PK is an autonumber and by definition cannot be changed. 2. Business rules regarding uniqueness can be implemented with unique indexes. 3. In the cases where one of the "natural" keys may validly be blank, I don't have an issue with a unique index whereas I would have an issue if I defined a compound primary key since PKs may not contain null values. SSN is a good example here (aside from the fact that there are laws prohibiting its use as a PK) since not everyone will have one (h1b visa holders for one) and there are only limited cases where a person actually has to provide one to the requestor. 4. Combo/List boxes require code behind the scenes to work if the unique identifier is compound. 5. As the hierarchy gets deeper, the compound key grows and joins become more unwieldy. It is way too easy to omit one field out of 5 in a join with disastrous results if the error isn't discovered quickly. The only pro for using autonumber PKs is that a lot of your tables will end up with lots of numeric columns that you would prefer to view as text when you open the table. The solution is NOT table level lookups, it is creating queries that join the tables so you can see the identifying "natural" key data from the parent table. You only have to create one of these views for each table so it's not like it is a great deal of work. Then for yourself (because ONLY you should be viewing tables or queries directly), just remember to open the query rather than the table when you want to see the natural key values. That's my opinion and I'm stickin' to it "M." wrote in message ... Dear all, Although many Microsoft Access books advise to set an autonumber field as primary index (a so called pseudo primary key), http://www.blueclaw-db.com/database_link_tables.htm advises to use real data to define a (composite) primary key. In summary, this results in the following two designs: Microsoft Acces books setup for Employee table Employee_ID (autonumber, primary key) SSN (social security number, composite index key1) Employee_Name (full employee name, composite index key2) composite index SSN + Employee_Name = unique BlueClaw setup for Employee table Employee_ID (autonumber, unique index) SSN (social security number, (composite) primary key1) Employee_Name (full employee name, (composite) primary key2) In both approaches, Employee_ID would be used as a foreign key in other tables to define the relationship with the Employee table. Are there any negative aspects associated with the BlueClaw approach? Pros of BlueClaw approach *Display of table is meaningful, because it's sorted on primary index *No cascaded update necessary of linked relationship fields in other tables, because autonumber is only used for linking tables and therefore will never change. *Prevention of duplicates is improved, since data fields are used to check for duplicates, instead of an (always unique) autonumber field this can also be achieved with the composite unique index as shown above in the Access books example. Cons of BlueClaw approach *??? I would appreciate your comments / opinion on the BlueClaw approach, because I currently have the feeling that I'm missing something that explains why so many people use autonumber fields as primary (artificial) key. If the BlueClaw approach is the best one, I'm considering to use it as a standard in new database design questions. Best regards, M. |
#49
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
Thanks,
That Google portal is a whole new way to look at it. Fred "Jamie Collins" wrote: On Dec 6, 3:11 pm, Fred wrote: Can someone tell me what this mere mortal is missing regarding how to look at these long threads? This is what I see: http://groups.google.com/group/micro...1e479a6765df2/ Jamie. -- |
#50
|
|||
|
|||
Data Primary key vs. Artificial (Autonumber) primary key
=?Utf-8?B?TS4=?= wrote in
: What still interests me, however, is how pepole prevent entry of duplicate records when they use a autonumber field as primary index. This choice doesn't offer you any guarantee that the real data in your record is accidentally entered a second time in a later record. In my opinion you would still need a unique single (e.g. ISBN number) or multifield index (e.g. book title + author name) to prevent duplicate entry. In the end, it's then a matter of taste whether you name the autonumber field index primary or the composite field(s) index primary. Well, if you're using a surrogate key, that doesn't mean you leave out the unique indexes on fields and combinations of fields that should also be unique. However, in many cases, composite keys can't be unique because you have to allow storage of Nulls. In that case, your app has to do duplicate checking of some sort. Given that with things like names, you can't rely on a unique index even when Nulls are disallowed (because Bob and Robert could still be the same person), you often end up putting some form of duplicate checking into your app even when you already have unique indexes on the fields you're checking. So, to me, having unique indexes is a question that is answered field by field (or composite key by composite key), and is wholly independent of which unique index you happen to use for your primary key. The unique indexes are a function of the data, while the primary key is a function of your database structure. One field or collection of fields may serve both functions, but they don't have to. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|