Design of address tables: Second attempt.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

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
 
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.
 
Looks fine to me. You need to review your own requirements and see if it
meets all of your specs.
 
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:-
Click to expand...
[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.
 
Back
Top