How to handle address lists?



An address list contains the following contact information:

Supplier name
Address Line 1
Address Line 1
Country (default = USA)
Email Address

Logically a decentralized business can have multiple physical addresses (2)
per supplier name (1). A centralized business can have multiple supplier
names (1) per physical address (2). Once the unique combination of supplier
name + physical address has been determined there will be an associated
email address (3) for email communication. Essentially it's a many 1) name
to 2) many address to 3) many email relationship.

Next, is it common practice to combine the all 8 fields in one table or
separate the logical groups into 3 tables + a resolver table containing the
ID (key) fields, and then link the ID PK field from this record to all other
data referencing contact (name + address + email).

If the more fully normalized schema is used, what is the best way to
maintain the underlying tables? If a new centralized supplier name (1) is
added an existing physical address (2) may already be present. The reverse
is also true if a new decentralized supplier address is added. This makes a
two step combo box limit-to-list process impossible because selecting either
(1) or (2) does not necessarily limit the list to the other piece of the
pair (1) & (2).

The database also contains other tables linked to contact information. It
is a large list for which the use of combo boxes is difficult.



Jeff Boyce

What you are describing does indeed sound like a many-many relationship.
Regardless of how you eventually facilitate the data entry (via forms!), the
underlying table structure will need to reflect this relationship, with, as
you mentioned, a third/resolver/junction table.

One mechanism for associating addresses with folks would be to create a form
that lets you pick a person (combobox) and lets you pick an address (also a
combobox) and saves the record in that third table.

Good luck!


Jeff Boyce
Microsoft Office/Access MVP

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question