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

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" wrote in message
news:1194170467.940647.62390@z9g2000hsf.googlegroups.com...
> 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

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

On Nov 4, 5:01 am, Chris Morley wrote:
> 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

I was looking at mork with respect to this recently. Mork is an older
Mozilla technology for storing things like addresses.
Supposing I have several siblings and that their last names are all
"Fairfield" and that I have all of my siblings in my address book. How
many times does "Fairfield" appear in the address book mork file?
Once!
We have a (hex) pointer say
11a=Last Name
and another
ffe=Fairfield
and so for each sibling we have a several pairs of pointers, one of
which is:
(11a^ffe)
I see some value in this in a web application which struggles
maintaining a db connection or there is concern about exposing other
more important data in the db to hackers. The data is just saved in
text files. We can interpret this data super fast with regular
expressions and reading a text file may be faster than sending off a
select query to some remote server.
I like this. But is it so efficient as to make it worthwhile? I don't
know. And the Mozilla crowd seems to be abandoning it for SQLite.

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

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: Repeated Attributes in tables (Address, contacts etc)

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" wrote in message
news:1194260695.814689.192280@v3g2000hsg.googlegroups.com...
> 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: Repeated Attributes in tables (Address, contacts etc)

I'll throw in my two cents, as well.

Many times having one table for multiple "types" of people can cause
security issues. You might not want everyone to see your HR data, or
Client list, etc.

If you wanted to keep a Employees Table, Customer Table, Shippers
Table, etc. then you could:


tblAddresses:
AddressID AutoNumber
EmployeeID Long Int, Allow Nulls
CustomerID Long Int, Allow Nulls
ShipperID Long Int, Allow nulls.


When you join the address table to the Employees Table on Employee ID,
it will only show employee Addresses. I've modeled database like this
in the past, and it works well.



Chris Nebinger



On Nov 5, 6:05 am, "Allen Browne" wrote:
> 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" wrote in message
>
> news:1194260695.814689.192280@v3g2000hsg.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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

Thanks for the replies. In keeping the common data in one table, and
moving specific data into sub-tables, wouldnt we need to perform a 1:1
join? This is where it starts getting complicated, i am using
CSLA .NET framework for my business object and typically business
objects are derived from individual tables. The code generator will
derive business objects over multiple tables as required but obviously
the more joins and tables involved the more complicated it is to
devlop. It certainly can handle 1:1 joins and 1:many joins (sub lists)
so maybe i will look into this to reduce the repeating attributes.

Many thanks,

Chris

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

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

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

Yes, you seem to be thinking well about this, Chris.

As I suggested, avoid subclassing unless it's really needed. Keeping it as
simple as possible is one of your goals, so if you can get away without the
extra tables, so much the better.

--
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" wrote in message
news:1194330690.016281.28130@d55g2000hsg.googlegroups.com...
> 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