Design of address tables: Second attempt.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Martin - 24 Dec 2003 02:08 GMT

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

Reply to this Message

Duane Hookom - 24 Dec 2003 05:36 GMT

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.

Signature

Duane Hookom
MS Access MVP

> Thank you for the replies.
>
[quoted text clipped - 54 lines]
> Please advise if there are any mistakes in the design above.
> Kind regards and thanks, Frank Martin

Reply to this Message

Frank Martin - 24 Dec 2003 06:52 GMT

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

> If your tblNameType contains fields with names like "Customer",
> "Supplier",... then you are creating an un-normalized table structure. You
[quoted text clipped - 60 lines]
> > Please advise if there are any mistakes in the design above.
> > Kind regards and thanks, Frank Martin

Reply to this Message

Duane Hookom - 25 Dec 2003 05:45 GMT

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

Signature

Duane Hookom
MS Access MVP

> No, these names are records in the field "NameType".
>
[quoted text clipped - 62 lines]
> > > Please advise if there are any mistakes in the design above.
> > > Kind regards and thanks, Frank Martin

Reply to this Message

Mike Sherrill - 04 Jan 2004 16:50 GMT

>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.

Signature

Mike Sherrill
Information Management Systems

Reply to this Message