Which is More Efficient - Lots of One Field Tables or Larger Multi-Field Tables

[Karl Burrows]

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!

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Karl Burrows]

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 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 <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

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

 

[Vincent Johns]

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/books/sampchap/6800.asp#100.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.