Re: Repeated Attributes in tables (Address, contacts etc)

Good question, Chris. I'm not sure there's a one-size-fits-all answer, so hopefully you will get a variety of approaches to consider.

Firstly, there's the issue of the kinds of entites that you deal with who have addresses: suppliers, customers, contacts, employees, and so on. Where ever possible, I tend to put all these entities into one table, with a generic name such as "clients." By putting any person/company you deal with in the one table, it makes it very easy to search, assign addresses, make/receive payments, and assign connections between them.

In many cases, one "client" can have multiple roles (e.g. an employee can also be a customer, or a supplier can also be a shipper), which suggests a many-to-many relation between Clients and Roles. You may also need to handle formal and/or ad hoc groupings of clients (e.g. the contacts for a business, members of a committee, and so on.) For a broad discussion of how this could be modelled, see:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

One you have all these entities as "clients", you can then address the question of how you handle addresses. In some cases, it would make sense to create an Address table, so you have a many-to-many relation between clients and addresses. In other scenarios, that would be overkill, and a one-to-many relation between clients and address would suffice.

The one-to-many (client-to-address) relation might be strictly less normalized, but if you have defined the groupings between clients adequately, you may be able to derive some addresses from the kinds of relationships that exist between the clients. For example, if John Smith works 2 days a week for Acme Corp, and 2 days a week for MM Corp, and you have the suitable addresses for these 2 companies, then you can derive the correct work address to use for John when you deal with him in one context, and the other address when you deal with him in the other context. This has some advantages over just assigning 2 work addresses to John himself.

Hope that gets you thinking in some useful directions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris Morley" <morleyc@xxxxxxxxx> wrote in message
news:1194170467.940647.62390@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi, i have found out as i have developed the database i've ended up
with a number of tables that have the same attributes.

For instance, my suppliers and customers have the same fields for the
address. Would it make more sense to create an additonal table and to
reference the addressID in the address table?

Some tables like employees have multiple addresses, like home address,
next of kin address etc. Now this is more of an issue for maintance as
if i want to add an additional address field (such as state) then i
would need to add that for each table that has address like fields.

Is this a comon and recommended way? Its not normilsation per se as
this isnt reducing the data it just makes it easier to maintain.

Also, how would i go about mixing a table of contacts, which could
either belong to supplier, customer company etc? A contact wouldnt be
able to have both a supllier and customerID as one would be blank,
would i be better off having a CompanyID FK in the contacts table, and
then in the Companies table a lookup domain value which defines wether
the company is supplier/cuustomer etc?

Appreciate any recommendations on good books for the basic design
scenarios and how they are done professionally. Spent an hour on
google without too much enlightenment hence the post here.

Many thanks in advance,

Chris

.

Relevant Pages

  • Re: The Art of (Killing) the Deal
    ... But, as I like to say, our biggest ... Your clients and customers are ... the hilt no matter how badly they screw up. ...
    (misc.invest.stocks)
  • The Art of (Killing) the Deal
    ... I usually write about investments. ... Your clients and customers are ... the hilt no matter how badly they screw up. ...
    (misc.invest.stocks)
  • Re: Access 07 Contemplations
    ... The new owners received the restaurant ... previous or the new customers. ... creative and add features and remove them to serve their own ... Clients just have no where else ...
    (comp.databases.ms-access)
  • Re: How much is to much? Why should my clients have to pay again?
    ... > clients have had a problem with this new fangled validation tool. ... > I assume you mean you have had customers complaining they could not ...
    (microsoft.public.windowsupdate)
  • Re: How much is to much? Why should my clients have to pay again?
    ... > clients have had a problem with this new fangled validation tool. ... I assume you mean you have had customers complaining they could not get the ... I don't know how you billed each one $200 (what did you do to fix the ...
    (microsoft.public.windowsupdate)