Design of address tables: Please advise.

[Frank Martin]

We are redesigning the address tables of our Access2000 database so as to
allow consolidation of old individual address tables such as Account
customers, Freight depots, Delivery addresses, Postal Addresses, Pickup
Addresses etc etc into one address system consisting of three new tables.
Also, we want to combine Customers and Suppliers in this one table.

These three tables will be:


tblNAME
NameID pK
(details)
|
| one
|
|
|
|
|many
|
tblADDRESS
NameID fK
TypeID fK
(details)
|
| many
|
|
|
|
|one
|
TblADDRTYPE
TypeID pK
details:-
Account
Freight
Pickup
Personal
Postal
Supplier
SupplierPostal
etc

We think that the tblNAME and the tblADDRTYPE have a many/many relation, and
that the tblADDRESS is a linking table between the two, and that the only
keys required in the tblADDRESS are the NameID and TypeID as composite IDs.
ie there is no need for an "AddressID".


We believe the above system will rationalize the old separate address tables
into one, and also:

1/ Allow to be shown all types of addresses per name

2/ Avoid the need for union queries to compile a customer/supplier
listing.

3/ Allow staff to have their own contact list within the main address
system.


Before we start converting our system, please advise if anybody can see any
mistakes in the above design.
We are still new to this.

Kind regards, Frank

 

[Steve Schapel]

Frank,

As far as I can see, the basic idea is correct. However, I would
change a few things.

First, I would definitely have an AddressID primary key field in the
tblADDRESS table.

Second, in a way you are technically correct in suggesting that there
is a "many-to-many" relationship between tblNAME and TblADDRTYPE, but
I feel it is possibly confusing to dignify the TblADDRTYPE with this
level of status... it is only ancillary lookup type data, not core
data, and if I correctly understand what you wrote, this table only
has one field, so I personally would not conceptualise the data
structure in this way. In fact, in most of my databases where I use
this type of idea for addresses, I do not have the address types in a
table at all, they are simply listed in the rowsource property of the
comboboxes on the forms... but I'll admit that the success of this
plan depends on there being no intention to ever add to the list.

Third, be careful with the address type options, and try to make them
mutually exclusive. For example, could there be a confusion between
"account" and "postal"? For another example, it does not seem to me
to be valid to have a category of "supplierpostal"... Supplier should
be a catagory of organisation as per the tblNAME table, and a postal
address is a postal address for the linked tblNAME record, whether it
is a supplier or whatever.

- Steve Schapel, Microsoft Access MVP

 

[Immanuel Sibero]

Hi

For the purpose you stated, it appears your design is adequate. I'm just
wondering about the fields "supplier" and "supplierpostal". You also
mentioned the need to combine Customers and Suppliers in one table. How do
you determine if a record in tblName is a customer or a supplier? From the
values of the "supplier" field in tblAddrType table?? Within this context,
I think the tblAddrType table may need a little work.

I want to bring up one of the normalization processes of eliminating
dependency among non-key fields in a table. It says all non-key fields
should depend and only depend on the primary key. To apply this rule, all
non-key fields in your tblAddrType should only depend on TypeID, but
'supplier' and 'supplierpostal' appear to (at least, partially) depend on
NameID (from tblName). So this is a deviation from a normalized table design
which can create problems.

I would suggest the designation of Customer vs. Supplier be handled in the
tblName. An example would be a flag indicating that a record is a customer
or a supplier, or a lookup function (the same way you implemented address
type, you would now have, say, tblNameType - Customer, Supplier, etc.)

This way, you wouldnt need the fields 'supplier', 'supplierpostal' in your
tblAddrType table. Better yet, in the future you may wish to add different
'types' of Name list - Associates, Affiliates etc etc. for whom you also
want to keep different addresses.


HTH
Immanuel Sibero

 

[Mike Sherrill]

We are redesigning the address tables of our Access2000 database so as to
allow consolidation of old individual address tables such as Account
customers, Freight depots, Delivery addresses, Postal Addresses, Pickup
Addresses etc etc into one address system consisting of three new tables.
Also, we want to combine Customers and Suppliers in this one table.

These are worthy goals.

These three tables will be:

[snip]

In the real world, there's a many-to-many relationship between an
address and the person or organization that "has" the address. And in
the real world, there's a difference between 1) an address, 2) a
mailing label, and 3) how an address is used (for pickup, delivery,
and so on). Here's a rough, minimal example.

Parties
--
<Party PK>
<other columns>

Addresses
--
<Address PK>
<other columns>

PartyAddresses
--
<Party PK> (foreign key, references Parties)
<Address PK> (foreign key, references Addresses)
<Usage>
<other columns>
(primary key <Party PK>, <Address PK>, <Usage>)

Contacts are altogether different. In the real world, contacts might
not have a known address--just an email address or a phone number.

We are still new to this.

You're in for a rough ride. Good luck.

 

[Frank Martin]

We are redesigning the address tables of our Access2000 database so as to
allow consolidation of old individual address tables such as Account
customers, Freight depots, Delivery addresses, Postal Addresses, Pickup
Addresses etc etc into one address system consisting of three new tables.
Also, we want to combine Customers and Suppliers in this one table.

These are worthy goals.

These three tables will be:

[snip]

In the real world, there's a many-to-many relationship between an
address and the person or organization that "has" the address. And in
the real world, there's a difference between 1) an address, 2) a
mailing label, and 3) how an address is used (for pickup, delivery,
and so on). Here's a rough, minimal example.

Parties
--
<Party PK>
<other columns>

Addresses
--
<Address PK>
<other columns>

PartyAddresses
--
<Party PK> (foreign key, references Parties)
<Address PK> (foreign key, references Addresses)
<Usage>
<other columns>
(primary key <Party PK>, <Address PK>, <Usage>)

Contacts are altogether different. In the real world, contacts might
not have a known address--just an email address or a phone number.

We are still new to this.

You're in for a rough ride. Good luck.

***************

We are having some luck already.

The tables are as described above, and we have constructed a query from all
four tables.

In this query we pull down the ID's from the "many" sided tables only, and
the other non-ID fields as required.

We have tested this in the query itself, which has inherited the lookup
combos from the relevant tables, to add successfully new addresses, and to
modify old ones.

We have made a form from this query via the form wizard , which is a main
form and one subform.

On this form the first step is to select the name type
(customer/consultant/personal/representative/etc) and then enter the name in
the next text box "Name".

We then enter the subform and select the address type
(Account/Supplier/Freight/Site/etc.) and then fill in all the address
fields. While still in this subform we can add more addresses for this new
customer.

Editing old addresses is done the same way.

Now, after further testing we plan to convert our existing application over
to this new address system.

Kind regards, Frank Martin

 

[Mike Sherrill]

We are having some luck already.

Celebrate your victories. That's what I always say.

We have tested this in the query itself, which has inherited the lookup
combos from the relevant tables, to add successfully new addresses, and to
modify old ones.

Don't use lookup fields in tables. They might be bearable in queries,
but most developers I know don't use them even in queries. Google the
newsgroups for "lookup fields" for details, or wait for someone else
to chime in.

 

[Frank Martin]

Thanks. I have removed the table lookups and reverted to text boxes, but
the inherited lookups have remained on the addresses form. Should I convert
these to text boxes and reconvert to combos?

Also do the remarks on table lookups apply to query lookups too?

Regards, Frank

Good advice on lookup fields. It's tempting to use lookup fields in tables.
MS certainly makes it convenient to do this. Anyone who's tempted to use
lookup fields in tables should read:

http://www.mvps.org/access/lookupfields.htm

So, use forms to implement lookup.

Immanuel Sibero

 

[Immanuel Sibero]

Hi Frank

the inherited lookups have remained on the addresses form. Should I convert
these to text boxes and reconvert to combos?

I'm not sure I understand your question here. But yes, you would use combo
boxes via the form for table fields that you originally defined with lookup
funtion.

Also do the remarks on table lookups apply to query lookups too?

Yes.

Immanuel Sibero

Thanks. I have removed the table lookups and reverted to text boxes, but
the inherited lookups have remained on the addresses form. Should I convert
these to text boxes and reconvert to combos?

Also do the remarks on table lookups apply to query lookups too?

Regards, Frank

 

[Mike Sherrill]

Thanks. I have removed the table lookups and reverted to text boxes, but
the inherited lookups have remained on the addresses form. Should I convert
these to text boxes and reconvert to combos?

I don't think you need to do that. The main problems with lookup
fields are the hidden side-effects they have on tables.

Also do the remarks on table lookups apply to query lookups too?

Lookup fields in queries don't have the same hidden side-effects they
have in tables, but they still hide the structure from the developer.
I think I'd have to say that their use in tables is a Bad Thing, and
their use in queries is a less bad thing.