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
|
|||
|
|||
Which is More Efficient - Lots of One Field Tables or Larger Multi-Field Tables
I am trying to decide how to "normalize" a large database. There are about
25 fields with many common values like State, County, Street Address, MapID, etc. I can normalize this table and create many linked tables with each of those common values, but that seems even less efficient. If I have 15 one field tables and one 10 field table to populate a query that is used for a form, is this a good way to approach it or is there another recommended way? Thanks! |
#2
|
|||
|
|||
Karl Burrows wrote:
I am trying to decide how to "normalize" a large database. There are about 25 fields with many common values like State, County, Street Address, MapID, etc. I can normalize this table and create many linked tables with each of those common values, but that seems even less efficient. How efficient it is depends on what kinds of transactions you intend to do once it's redesigned. If you do lots of additions and deletions, you might be better off with large records and few links. If you do lots of updates (= changing values of fields in existing records), then you might not want to have many copies of any one field, so you'd want more linked lists. This is especially true if the updating process involves much human interaction (but I assume that that's not the case here). If I have 15 one field tables and one 10 field table to populate a query that is used for a form, is this a good way to approach it or is there another recommended way? Thanks! The main use I can think of for a 1-field table would be as a source of choices for a selection list (e.g., names of states). I guess you would copy each selected value into a field in a record in another table. Is that how you're using them? -- Vincent Johns Please feel free to quote anything I say here. |
#3
|
|||
|
|||
It is to track lots, so the initial drop-down values will be set and then
not changed (address, city, county, state, MapID, etc.). At that point, then just dates and fees would be maintained. As far as additions and deletions, usually additions are done in large groups as new neighborhoods are added, so there may be no new additions for 6 months and then 600 at once. Then again, the initial values would be set for the drop-down values and then not changed again. I was trying to create a table for each value of State, City, County, MapID and other common values to try to eliminate duplicate record values in the main table, but wasn't sure if that was less efficient by having to reference 15 separate tables for individual field values. I have debated this over and over on how to do this. Based on this, what is your suggestion? Thanks! "Vincent Johns" wrote in message ink.net... Karl Burrows wrote: I am trying to decide how to "normalize" a large database. There are about 25 fields with many common values like State, County, Street Address, MapID, etc. I can normalize this table and create many linked tables with each of those common values, but that seems even less efficient. How efficient it is depends on what kinds of transactions you intend to do once it's redesigned. If you do lots of additions and deletions, you might be better off with large records and few links. If you do lots of updates (= changing values of fields in existing records), then you might not want to have many copies of any one field, so you'd want more linked lists. This is especially true if the updating process involves much human interaction (but I assume that that's not the case here). If I have 15 one field tables and one 10 field table to populate a query that is used for a form, is this a good way to approach it or is there another recommended way? Thanks! The main use I can think of for a 1-field table would be as a source of choices for a selection list (e.g., names of states). I guess you would copy each selected value into a field in a record in another table. Is that how you're using them? -- Vincent Johns Please feel free to quote anything I say here. |
#4
|
|||
|
|||
"Karl Burrows" wrote in
: I am trying to decide how to "normalize" a large database. There are about 25 fields with many common values like State, County, Street Address, MapID, etc. I can normalize this table and create many linked tables with each of those common values, but that seems even less efficient. Normalisation is NOT ABOUT EFFICIENCY or redundancy or repetition or disk space or performance or duplication or anything even remotely related to real life concepts. (sorry for shouting!) Normalisation is about integrity. Normalisation is keeping the data clean and free from ambiguity. Normalisation is about protection. Early attempts at relational database systems sucked for performance, used up buildings full of hard disk stacks and got laughed at by all the old systems managers used to their network and heirarchy architectures. Except that R produced correct results compared to the inconsistent and contradictory garbage that came from non-R. Look at this: Product Producer-Name Producer-Address ======= ============= ================ Beans Heinz Halifax Sausages Blackwell London Bacon Blackwell London Pudding Heinz Leeds Tomato Blackwell London Mushroom Blackwell London Cheese Heinz Halifax Butter Heinz Halifax Bread Blackwell London and so on. Now, Question 1: What is the address of Heinz Foods? Question 2: What is the address of Crossleys Catering? (hint: we just deleted their one product because it is being upgraded to a new flavour) That is the problem with non-R non-normalised designs: essentially, that information is lost as a side effect of other unrelated updates. It is only with R that the designer can _guarantee_ the correctness of the design -- and there is forty years' research backing up that claim, which is more than any other database paradigm so far. Just thought I'd clear that one up for the record! B Wishes Tim F |
#5
|
|||
|
|||
Karl Burrows wrote:
It is to track lots, so the initial drop-down values will be set and then not changed (address, city, county, state, MapID, etc.). At that point, then just dates and fees would be maintained. As far as additions and deletions, usually additions are done in large groups as new neighborhoods are added, so there may be no new additions for 6 months and then 600 at once. Then again, the initial values would be set for the drop-down values and then not changed again. I was trying to create a table for each value of State, City, County, MapID and other common values to try to eliminate duplicate record values in the main table, but wasn't sure if that was less efficient by having to reference 15 separate tables for individual field values. I have debated this over and over on how to do this. Based on this, what is your suggestion? Thanks! I hate to sound wishy-washy here, but you probably won't go wrong either way. I usually like to normalize my databases, using numerous links, to make auditing the values easier. (With lots of smallish tables, it's not hard to examine the contents of one to look for possible mistakes and correct them. But it wouldn't be difficult to write a query to do that.) As far as computer efficiency is concerned, my guess is that you're nowhere close to exhausting the capabilities of Access. For details about those, look in Access Help for the topic "Microsoft Access specifications". Extreme cases will be obvious to you -- if you have 100 records with the same long name in them, you would clearly save space by recording that name just once in a separate table and linking to it. Each link occupies 4 bytes. Conversely, US Postal Service abbreviations of state names use only 2 bytes each, so linking to a table of states wouldn't do much to save space. Concerning efficiency of human effort to maintain/update the tables, whether you use a few large tables or (my choice) several smaller linked ones, it's still a good idea to set up Access queries and forms to display or input limited amounts of information at a time. Using queries, or forms based on them, will to some extent hide the details of how you have organized your tables. You can specify that a given query be read-only (by declaring its type to be "Snapshot") and thus you can protect a table or some fields in it from being accidentally changed when you use that query as the data source. Your queries or forms can present users with list boxes or combo boxes to limit the choices to values you know are likely to be valid, cutting down on misspellings, etc. (Sadly, it can't limit them to choices that are correct -- otherwise, you could simply have Access enter the information.) For additional information, a discussion of normalization is available at http://www.microsoft.com/mspress/boo...p/6800.asp#100. -- Vincent Johns Please feel free to quote anything I say here. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with relationship plase | Rock | General Discussion | 5 | July 4th, 2005 03:54 AM |
Help with relationship plase | Rock | Database Design | 5 | July 4th, 2005 03:54 AM |
Follow up Question – Convert one field into three fields | Doug | General Discussion | 2 | April 9th, 2005 10:21 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
ASK Field Behavior | Greg | Mailmerge | 9 | July 2nd, 2004 02:44 PM |