R
Rolls
An address list contains the following contact information:
1)
Supplier name
2)
Address Line 1
Address Line 1
City
State
Country (default = USA)
PostalCode
3)
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.
1)
Supplier name
2)
Address Line 1
Address Line 1
City
State
Country (default = USA)
PostalCode
3)
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.