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
|
|||
|
|||
Lookup fields in tables bad?
Good day all,
I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. My question is two-fold. First; what is the risk and what bad things can happen to the wayward soul who unwittingly builds lookup fields into his tables, and second; if such practice should be avoided like the plague, how does one go about eliminating them after the fact? Maybe one more question; I also use field lists and value lists at the table level. Is this the same, and just as bad as having an SQL lookup field in a table? In a copy of a database I’m working on now, I changed all of the table level lookup fields (SQL type) to straight text boxes and I’m waiting now for that copy to implode…but it hasn’t yet, and all of my forms are working correctly. (I have lots and lots of lookups as the Row Source property.) Since I changed the table fields to Text Boxes, I haven’t tried to enter any new data…maybe Armageddon awaits me there? I rarely enter data directly into my tables; and even when I do it's only after a first pass at building one, and just before I create a form for data entry purposes. Since my forms all have lookup fields in them, will my house of cards tumble only if I were to enter data directly into a table? If anyone has some good insight on this, or would simply to poke fun at me for my ignorance or even just offer anecdotal comments on what may be my impending doom, I would appreciate any input at all. Thank you in advance. Best regards, RL |
#2
|
|||
|
|||
Lookup fields in tables bad?
On Thu, 22 Jan 2009 17:04:03 -0800, Robert5833 wrote:
Good day all, I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. All of the advantages of lookup fields in tables are only there if you work directly with tables. Nobody who develops serious apps for people create those apps so users can work directly in tables. They use forms and reports for the interface and when you do that lookup fields in tables lose all their advantages and pick up a few disadvantages. The most notable is that your tables display data that is not really there which can be confusing when using them in queries and forms and reports. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Lookup fields in tables bad?
Okay, lets see if we can clarify things for you here.
In table design, if you choose a field of type "Lookup Wizard", you fire up a wizard that many of us don't like. We tend to avoid this wizard because of its side effects. You have probably read this: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm But no one is saying that you should not use foreign keys. Every non-trivial database has lookup tables -- small tables that contain the valid values for a field in a table that looks them up. That is the only way to correctly normalize your data. There is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.) Sounds like you may have done that in some cases. Next the question arises as to whether your main table should use a text box or a combo box for the foreign key field. Combos are great on forms, but many developers avoid them in the table because it masks what data is really here. For example, if you use the Lookup Wizard, you get a field of type Number, but it displays the text. That's confusing for developers and newbies alike: you actually have to open the table in design view in order to build a WHERE clause on the field, because you don't know what delimiters you need just by looking at the data. You also raise the possibility of using value lists rather than a lookup table. IMHO, that's the worst possible solution. It's trivial to let the user add more records to a lookup table, but modifying the items in a lookup list requires design changes. For more info on this, see: http://allenbrowne.com/ser-27.html#ValueList So, FWIW, my recommendations would be: - Do use foreign keys. - Avoid the Lookup wizard. - Use natural keys where appropriate. - Use combos in your table only rarely. - A Value List in a combo in a table is too inflexible. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Robert5833" wrote in message ... Good day all, I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. My question is two-fold. First; what is the risk and what bad things can happen to the wayward soul who unwittingly builds lookup fields into his tables, and second; if such practice should be avoided like the plague, how does one go about eliminating them after the fact? Maybe one more question; I also use field lists and value lists at the table level. Is this the same, and just as bad as having an SQL lookup field in a table? In a copy of a database I’m working on now, I changed all of the table level lookup fields (SQL type) to straight text boxes and I’m waiting now for that copy to implode…but it hasn’t yet, and all of my forms are working correctly. (I have lots and lots of lookups as the Row Source property.) Since I changed the table fields to Text Boxes, I haven’t tried to enter any new data…maybe Armageddon awaits me there? I rarely enter data directly into my tables; and even when I do it's only after a first pass at building one, and just before I create a form for data entry purposes. Since my forms all have lookup fields in them, will my house of cards tumble only if I were to enter data directly into a table? If anyone has some good insight on this, or would simply to poke fun at me for my ignorance or even just offer anecdotal comments on what may be my impending doom, I would appreciate any input at all. Thank you in advance. Best regards, RL |
#4
|
|||
|
|||
Lookup fields in tables bad?
"Allen Browne" wrote in
: here is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.) I would add to all those things the question of whether the values are ever going to be updated. If they can change, then I'd use an Autonumber surrogate key, instead of the natural key. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#5
|
|||
|
|||
Lookup fields in tables bad?
Thank you Rick! Great info! I'll post a reply to all of sorts...
Robert "Rick Brandt" wrote: On Thu, 22 Jan 2009 17:04:03 -0800, Robert5833 wrote: Good day all, I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. All of the advantages of lookup fields in tables are only there if you work directly with tables. Nobody who develops serious apps for people create those apps so users can work directly in tables. They use forms and reports for the interface and when you do that lookup fields in tables lose all their advantages and pick up a few disadvantages. The most notable is that your tables display data that is not really there which can be confusing when using them in queries and forms and reports. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Lookup fields in tables bad?
Hi Allen,
Thanks so much for the great outline on this subject, and things are much clearer to me now. In fact I have frequented your site and others, trying to gain what knowledge I can from the pros, and I spend hours reading the helpful posts here, and those interesting and helpful resources you and others make available to guys like me who are just beginning. And I thank everyone in this group for their generosity, and their willingness and enthusiasm to help others. These discussion groups have been an invaluable resource for me. Back to the conversation at hand; as for primary and foreign keys, that hasn’t been too much of a struggle for me, but I’ve been less apt to use a natural key and almost always go with the Autonumber variety. But given your input I’m beginning to see that the options are there, although it will take me a while to understand when one is more desirable than the other. My bigger struggles have been with all of the other lookup fields that I build into my tables that over time I began to realize didn’t add any value at all (I do use forms exclusively for data entry, and all of that functionality is in them as well). Not to mention (and as you and others have pointed out) the problems that result in queries and code when the field properties aren’t what one thinks they might be given the data they display. My overuse of lookup fields in tables seems to be a carryover from a time when I didn’t know how to use forms well enough, and didn’t know how to build the power of lookup fields and controls into them. That and the fact that the Lookup Field Wizard was just so handy and easy to use… And interestingly, for all of the hours I have spent reading the material on the MS Access DN site, not a single mention that I can recall of the pitfalls? I’d like to think that I only use lookup fields in tables when they are foreign key references, but just given the quantity of them in my tables I’m not sure I understand when a foreign key could be a text box, or when it must be a combo box with lookup? (I would be grateful if you or someone could clarify that point for me; what is the rule, logic, or rationale for one or the other.) With regard to your comments and those of others about the use of Value Lists, Field Lists, List Boxes and the like, I’m much clearer now on their limitations. While I have used them on occasion, it has only been when I have a short list of selections, and only when I know those selections aren’t likely to change. When I do use them, I incorporate the Limit to List functions to keep them from growing. But I have found it difficult to manipulate or process data with them, so I have for the most part left them behind opting for an additional table instead and now I may eliminate them altogether. To be sure, I have a long, long way to go; but I look forward to reading more of your writings and the writings of others on this and other topics. I don’t want to abuse or monopolize anyone’s time here, so I’ll go back to reading everything I can find on this subject, and post again if I have any specific questions. Thanks again for the post! Best regards, Robert "Allen Browne" wrote: Okay, lets see if we can clarify things for you here. In table design, if you choose a field of type "Lookup Wizard", you fire up a wizard that many of us don't like. We tend to avoid this wizard because of its side effects. You have probably read this: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm But no one is saying that you should not use foreign keys. Every non-trivial database has lookup tables -- small tables that contain the valid values for a field in a table that looks them up. That is the only way to correctly normalize your data. There is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.) Sounds like you may have done that in some cases. Next the question arises as to whether your main table should use a text box or a combo box for the foreign key field. Combos are great on forms, but many developers avoid them in the table because it masks what data is really here. For example, if you use the Lookup Wizard, you get a field of type Number, but it displays the text. That's confusing for developers and newbies alike: you actually have to open the table in design view in order to build a WHERE clause on the field, because you don't know what delimiters you need just by looking at the data. You also raise the possibility of using value lists rather than a lookup table. IMHO, that's the worst possible solution. It's trivial to let the user add more records to a lookup table, but modifying the items in a lookup list requires design changes. For more info on this, see: http://allenbrowne.com/ser-27.html#ValueList So, FWIW, my recommendations would be: - Do use foreign keys. - Avoid the Lookup wizard. - Use natural keys where appropriate. - Use combos in your table only rarely. - A Value List in a combo in a table is too inflexible. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Robert5833" wrote in message ... Good day all, I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. My question is two-fold. First; what is the risk and what bad things can happen to the wayward soul who unwittingly builds lookup fields into his tables, and second; if such practice should be avoided like the plague, how does one go about eliminating them after the fact? Maybe one more question; I also use field lists and value lists at the table level. Is this the same, and just as bad as having an SQL lookup field in a table? In a copy of a database I’m working on now, I changed all of the table level lookup fields (SQL type) to straight text boxes and I’m waiting now for that copy to implode…but it hasn’t yet, and all of my forms are working correctly. (I have lots and lots of lookups as the Row Source property.) Since I changed the table fields to Text Boxes, I haven’t tried to enter any new data…maybe Armageddon awaits me there? I rarely enter data directly into my tables; and even when I do it's only after a first pass at building one, and just before I create a form for data entry purposes. Since my forms all have lookup fields in them, will my house of cards tumble only if I were to enter data directly into a table? If anyone has some good insight on this, or would simply to poke fun at me for my ignorance or even just offer anecdotal comments on what may be my impending doom, I would appreciate any input at all. Thank you in advance. Best regards, RL |
#7
|
|||
|
|||
Lookup fields in tables bad?
Hi David,
Thank you for the reply. I use almost exclusively the Autonumber surrogate key; maybe to a fault? On occasion I find that a table with just a few records might lend itself to use of a natural key, but I don't know enough about what I can, or cannot do with the data later, so to be safe I opt for the Autonumber. I suppose as time goes by and I gain a lot more knowledge than I have today, that point or question will resolve itself. Thanks again for the post! Best regards, Robert "David W. Fenton" wrote: "Allen Browne" wrote in : here is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.) I would add to all those things the question of whether the values are ever going to be updated. If they can change, then I'd use an Autonumber surrogate key, instead of the natural key. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#8
|
|||
|
|||
Lookup fields in tables bad?
On Thu, 22 Jan 2009 20:33:01 -0800, Robert5833
wrote: I’d like to think that I only use lookup fields in tables when they are foreign key references, but just given the quantity of them in my tables I’m not sure I understand when a foreign key could be a text box, or when it must be a combo box with lookup? (I would be grateful if you or someone could clarify that point for me; what is the rule, logic, or rationale for one or the other.) A foreign key can *never* be a textbox, nor can it ever be a combo box! Textboxes and combo boxes are display tools. Keys (primary or foreign) are fields in a Table. Those fields can be displayed - on a form or report, or thanks to some questionable design decisions by the Access development team, in tables - but don't confuse the data *storage* with the data *presentation*. Primary and foreign keys are logical entities defining the relationships between data. A Primary Key is a field - or a set of up to ten fields - which uniquely identify a record in a table. A Foreign Key is a field - or a set of up to ten fields - containing the same data as the Primary Key of some other table, and serving as a link to that table. Any field (whether it's a primary key, a foreign key, or not used as a key at all) can be displayed using the various data display tools that Access provides - or for that matter, not displayed at all. For instance, if I use an autonumber primary key (which I do), it will NOT be displayed to the user at all; the same applies to the Long Integer foreign keys which are related to it. The natural key/surrogate key argument can get long and heated. I'll use natural keys when they're short, stable, and unique, which actually leaves out a lot of fields that otherwise might be candidates. People's names, for example, fail on all three counts! Some examples where natural keys may be appropriate are State two-letter codes (if your addresses are entirely or mostly in the United States and Canada); there's only one state/province with ID as a code, two letters is suitably short, and they rarely change (Canada added NU and NT, Nunavut and Northwest Territory, a few years ago); five digit ZIP codes are another reasonable choice. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Lookup fields in tables bad?
Natural verses artificial keys
===================== You'll find diverse views on this, and I encourage you to read contrary views to get a balanced perspective. I personally find natural keys handy for several reasons: a) If you are going to require and uniquely index a single field in a lookup table (such as a category or type), I see no point in adding another artificial key as well. Keep it as simple as possible. b) The foreign key (i.e the field in the other table that looks up this Text field) is also Text. It's humanly meaningful, and sometimes reduces the number of additional lookup tables you need in your queries. c) The interface is easier/more flexible, avoiding several problems that occur if a combo's display column is not the bound column. For example: - Combo doesn't go blank in datasheet/continuous form view if you filter its RowSource. - Filters based on the combo are less problematic (and differ based on version of Access.) - Limit to List is optional (where you don't enforce referential integrity, or where the combo's RowSource is filtered.) - All versions of Access have display bugs where you filter a combo's RowSource (e.g. in Form_Current.) You avoid these problems if the bound column is the display column. In summary, using a simple, short, required, unique text field as a natural key is a simpler design, and avoids some limitations, complexities, and bugs in the Access interface. Cascading updates/deletes ===================== If you do decide to use natural keys, the question arises about whether you should use cascading updates. Again, informed people like Tony Toews and David Fenton don't like them and you've been referred to Tony's page explaining why. I do use them (sometimes.) If a category has been misspelled, it makes sense to me to allow the user to correct the spelling in the form where they manage the lookup values, and have JET cascade the update to the related tables. I almost never use cascading deletes on this kind of data. You really don't want all the records removed from your main table just because some idiot thought the category was no longer useful and decided to delete it. On occasion, I will use cascade-to-null. This means that the foreign key is set to Null in the related table when you delete the category from the lookup table, so the records are not lost: they become uncategorized. If you want to read more about that, see: Cascade to Null Relations at: http://allenbrowne.com/ser-64.html Overusing lookups ============== Not sure what examples you have in mind. All my databases have numerous lookup tables (typically between 5 and 40, depending on the complexity of the database.) The only cases where I don't bother creating a lookup table is where the choices are extremely limited and set in concrete, such as "M"/"F"/Null for Gender, or True/False/Null (using a Number field), of a field that holds a number represnting a data type (values for dbText, dbLong, etc.) Interfacing fields ============ John has explained the view that tables are to hold fields so combos don't belong there at all. That's a rule I follow about 90% of the time, but I sometimes use a combo in the table - mostly for the examples above where the RowSource is a Value List. Some of this stuff you decide by experience. Have a go at different approaches, and see what works for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Robert5833" wrote in message ... Hi Allen, Thanks so much for the great outline on this subject, and things are much clearer to me now. In fact I have frequented your site and others, trying to gain what knowledge I can from the pros, and I spend hours reading the helpful posts here, and those interesting and helpful resources you and others make available to guys like me who are just beginning. And I thank everyone in this group for their generosity, and their willingness and enthusiasm to help others. These discussion groups have been an invaluable resource for me. Back to the conversation at hand; as for primary and foreign keys, that hasn’t been too much of a struggle for me, but I’ve been less apt to use a natural key and almost always go with the Autonumber variety. But given your input I’m beginning to see that the options are there, although it will take me a while to understand when one is more desirable than the other. My bigger struggles have been with all of the other lookup fields that I build into my tables that over time I began to realize didn’t add any value at all (I do use forms exclusively for data entry, and all of that functionality is in them as well). Not to mention (and as you and others have pointed out) the problems that result in queries and code when the field properties aren’t what one thinks they might be given the data they display. My overuse of lookup fields in tables seems to be a carryover from a time when I didn’t know how to use forms well enough, and didn’t know how to build the power of lookup fields and controls into them. That and the fact that the Lookup Field Wizard was just so handy and easy to use… And interestingly, for all of the hours I have spent reading the material on the MS Access DN site, not a single mention that I can recall of the pitfalls? I’d like to think that I only use lookup fields in tables when they are foreign key references, but just given the quantity of them in my tables I’m not sure I understand when a foreign key could be a text box, or when it must be a combo box with lookup? (I would be grateful if you or someone could clarify that point for me; what is the rule, logic, or rationale for one or the other.) With regard to your comments and those of others about the use of Value Lists, Field Lists, List Boxes and the like, I’m much clearer now on their limitations. While I have used them on occasion, it has only been when I have a short list of selections, and only when I know those selections aren’t likely to change. When I do use them, I incorporate the Limit to List functions to keep them from growing. But I have found it difficult to manipulate or process data with them, so I have for the most part left them behind opting for an additional table instead and now I may eliminate them altogether. To be sure, I have a long, long way to go; but I look forward to reading more of your writings and the writings of others on this and other topics. I don’t want to abuse or monopolize anyone’s time here, so I’ll go back to reading everything I can find on this subject, and post again if I have any specific questions. Thanks again for the post! Best regards, Robert "Allen Browne" wrote: Okay, lets see if we can clarify things for you here. In table design, if you choose a field of type "Lookup Wizard", you fire up a wizard that many of us don't like. We tend to avoid this wizard because of its side effects. You have probably read this: The Evils of Lookup Fields in Tables at: http://www.mvps.org/access/lookupfields.htm But no one is saying that you should not use foreign keys. Every non-trivial database has lookup tables -- small tables that contain the valid values for a field in a table that looks them up. That is the only way to correctly normalize your data. There is a debate about whether the lookup table should have a numeric field as its primary key (in which case your foreign key field will also be a Number), or whether you should use a natural key (Text type.) If you have a lookup table for categories, each category name will be unique. If they are also relatively brief (just a couple of dozen characters), it makes sense to me to use that as the natural key instead of introducing an artificial key (such as an AutoNumber.) Sounds like you may have done that in some cases. Next the question arises as to whether your main table should use a text box or a combo box for the foreign key field. Combos are great on forms, but many developers avoid them in the table because it masks what data is really here. For example, if you use the Lookup Wizard, you get a field of type Number, but it displays the text. That's confusing for developers and newbies alike: you actually have to open the table in design view in order to build a WHERE clause on the field, because you don't know what delimiters you need just by looking at the data. You also raise the possibility of using value lists rather than a lookup table. IMHO, that's the worst possible solution. It's trivial to let the user add more records to a lookup table, but modifying the items in a lookup list requires design changes. For more info on this, see: http://allenbrowne.com/ser-27.html#ValueList So, FWIW, my recommendations would be: - Do use foreign keys. - Avoid the Lookup wizard. - Use natural keys where appropriate. - Use combos in your table only rarely. - A Value List in a combo in a table is too inflexible. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Robert5833" wrote in message ... Good day all, I read recently (having followed some links to various Access related websites from posts here) that one should avoid altogether the use of lookup fields in tables. Though I am indeed a novice at this, I have created a number of simple databases over the years, and in my limited understanding thought lookup fields were one of the benefits of a relational database? So, I created lots of lookup fields in my tables...lots and lots of them. My question is two-fold. First; what is the risk and what bad things can happen to the wayward soul who unwittingly builds lookup fields into his tables, and second; if such practice should be avoided like the plague, how does one go about eliminating them after the fact? Maybe one more question; I also use field lists and value lists at the table level. Is this the same, and just as bad as having an SQL lookup field in a table? In a copy of a database I’m working on now, I changed all of the table level lookup fields (SQL type) to straight text boxes and I’m waiting now for that copy to implode…but it hasn’t yet, and all of my forms are working correctly. (I have lots and lots of lookups as the Row Source property.) Since I changed the table fields to Text Boxes, I haven’t tried to enter any new data…maybe Armageddon awaits me there? I rarely enter data directly into my tables; and even when I do it's only after a first pass at building one, and just before I create a form for data entry purposes. Since my forms all have lookup fields in them, will my house of cards tumble only if I were to enter data directly into a table? If anyone has some good insight on this, or would simply to poke fun at me for my ignorance or even just offer anecdotal comments on what may be my impending doom, I would appreciate any input at all. Thank you in advance. Best regards, RL |
#10
|
|||
|
|||
Lookup fields in tables bad?
"All of the advantages of lookup fields in tables are only there if you
work directly with tables." to that I'd add "or queries" |
Thread Tools | |
Display Modes | |
|
|