Repeated Attributes in tables (Address, contacts etc)

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

Hi Allen,
>
Thanks for the insight, i think my post got lost so i have re-typed
the message. Sorry if double posts.
>
I like the idea of having all contacts in one table, but in my case
for employees i would keep additional fields such as salary, paassport
number etc, basically fields which i wouldnt keep for the suppliers or
customers. As the information is not 100% common im still confused how
i can make this flexible but maintain the additional fields above, or
perhaps i should stick with the seperate tables and have an address
fields table such as:
>
Address Table:
AddressID
Address
State
Country
...etc
>
EmployeesAddresses (PK on all columns)
CustomerID
AddressID
EmployeeAddressType (Home, Work, next of kin)
>
SuppliersAddresses (PK on both columns)
SupplierID
AddressID
>
This way i get the address fields which are common into one table, and
the specific information can sit in Employees, Customers, Supplier
tables etc.
>
I do like the idea of the roles, but i have one question. Say for
example i have an HoursBooked table which stores what employees have
been working on what project (EmployeeID, ProjectID, hours). The
employee is defined as a contact which has a employee role. Now, i
enter the details in the table for a particular employee, and then at
some point in the future the Employee role is removed from that
contact. On the hours entry form i am doing a lookup of employee's
only (contacts who have an employee role), howeverr as that contact is
no longer an employee they will not be listed but at the same time the
record is still there and we have a problem as the name cannot be
found as it doesnt exist in the list. I can see it becomes more
flexible but i have to include additional checking in my business
object tier to ensure everything stays in sync and remains logical.
>
Are there any specific pointers to the above scenarios?
>
Many thanks,
>
Chris

Re your first question, you can subclass if necessary. That is, the generic
"client" table contains all the fields that are relevant for most clients.
Then use related tables for the additional fields specific to each specific
subtype. Sometimes that's the best way to go; other times it is overkill,
i.e. you can just leave those fields in the client table, but when you build
the interface for the employees you include the fields for them on that
form.
>
Your question about roles that cease to be relvant is an issue. One approach
is to include a StartDate (when the client first took that role) and EndDate
(when the client ceased holding that role, null for current roles.) In this
way, you maintain the relevant information over time, and you can identify
those who are currently active or inactive in the role. If you are using a
combo, you can then sort the clients so the inactive ones are listed after
the active ones (with " (inactive)" after their name) so their names still
appear in the combo correctly instead of showing as blank.
>
It does involve more checking, as you say. Depends whether the flexibility
you gain is worth the effort to develop, and the answer will be different in
different scenarios.
>
As always, the goal is the very simplest solution that will cope with all
cases you could need to handle.
>
--
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" <morl...@gmail.comwrote in message
>
news:1194260695.814689.192280@v3g2000hsg.googlegro ups.com...
>
>
>>>>>>>>>>>>
- Show quoted text -

Just to say, If the CSLA business objects is going off topic please
ignore that part as i am using that for web front end and access for
the desktop front end so the 1:1 joins are still applicable and i am
interested in that part not business objects per se.
>
Many Thanks,
>
Chris