Design of address tables: Second attempt.

[Frank Martin]

Thank you for the replies.



With your advice we have altered the structure to separate the address types
and the name types by adding another table called tblNAMETYPE, so the system
now involves four tables, which are shown below:-

tblNAMETYPE
NameTypeID (pK)
Customer
Supplier
Consultant
Personal
Representative
Contractor
etc
|
|one
|
|many
|
tblNAME
NameID (pK)
NameTypeID (fK)
(details)
|
|one
|
|many
|
tblADDRESSES
AddressID (pK)
NameID (fK)
AddrTypeID (fK)
(details)
|
|many
|
|one
|
tblADDRTYPE
AddrTypeID (pK)
HeadOffice
Account
Site
Freight
DepotFOB
Residential
etc.

As suggested we thought of boolean switches to differentiate between
'customer' & 'supplier' in the tblNAMES, but another table 'tblNAMETYPE'
will allow easier addition of new categories later on.

Also as suggested we have included an 'AddressID' in tblADDRESSES which
might give flexibility in future designs.

Please advise if there are any mistakes in the design above.
Kind regards and thanks, Frank Martin

 

[Duane Hookom]

If your tblNameType contains fields with names like "Customer",
"Supplier",... then you are creating an un-normalized table structure. You
should not be using data values as field names.

 

[Frank Martin]

No, these names are records in the field "NameType".

 

[Duane Hookom]

Looks fine to me. You need to review your own requirements and see if it
meets all of your specs.

 

[Mike Sherrill]

With your advice we have altered the structure to separate the address types
and the name types by adding another table called tblNAMETYPE, so the system
now involves four tables, which are shown below:-

[snip]

I think you should spend less time thinking about tables and more time
thinking about the real world.

For example, "address type" usually means there's some logical
difference among the named types. That is, it means "site" addresses
and "freight" addresses are drawn from different domains.

I don't believe that. Do you?

No, the real difference between a "site" address and a "freight"
address is simply how the address is to be used. (The first might
identify the location of a site; the second, where to send shipments.)

You can model parties and their addresses independently of the
relationships between parties and independently of how addresses might
be used. And you should, because parties and their addresses *exist*
independently of the relationships between parties and independently
of how addresses might be used.