On 22 Apr 2005 05:59:21 -0700,
al********@it4us.nl (Paul) wrote:
Hi,
When should I use a list (in table properties: like
Ford;Mercedes;BMW;Audi )
and when should I use a lookup table??
And second question: IF I use a lookup table, should I always make a
relation (1-to-many) in my relation scheme?
Any help is greatly appreciated, I'm a bit puzzled.
thx
Paul
Generally speaking, lists should be kept in tables, and lookup items should be
identified by a surrogate key (usually an Autonumber), not by a name.
Let's take your example above of automobile brands, and say there's a table of
automobiles, each one having a brand as an attribute. Ler's also say you
used a value list combo box for the selection like Ford;Mercedes;BMW;Audi.
Now, let's say you've decided you need this data to be editable in more than
one form, so you create the new form and copy the combo box from the first
form to the second form. You now have 2 combo boxes with the same list.
Next, let's say 2 months later, you decide you want to add Fiat to the list,
and you also want to change the name of Merceded to Mercedes Benz. So - you
track down the first form, but you forget about the second form. Now, the
second form won't allow you to pick Fiat, and any Mercedes Benz selected in
the first form won't be the same as the Mercedes items selected in the second
form.
Additionally, what happens when you decide you need to sort cars by something
like manufacturer's home country? Now, you'll have to have an auto brand
table, so there's somewhere to store the country.
So, now let's say, based on the discussion above, we agree that we want to use
a table for auto brands, but we use the name as the identifier, not a
surrogate key.
The first consequence of this is that the database is larger than it needs to
be because the entire brand name is duplicated everywhere it is used, rather
than the mere 4-bytes it would take to store a numeric ID. The next
consequence is that we can't safely rename a brand name unless we also change
it everywhere it's used. We can use a cascade-updating relationship to have
Access fix that up for us, but if there are a lot of records affected, this
can be a massive update that could mess up other users trying to enter or edit
data in a multi-user system, and there's would still be the issue of the
wasted space to store the duplicate name information everywhere.
What we're really talking about here is database normalization theory. If you
want a deeper understanding, find a good book on relational database design.
