Repeated Attributes in tables (Address, contacts etc)

[Chris Morley]

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

[lyle]

On Nov 4, 5:01 am, Chris Morley <[e-mail]:

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.

[Chris Morley]

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

[Allen Browne]

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" <mo*****@gmail.comwrote in message
news:11**********************@v3g2000hsg.googlegro ups.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

[chris.nebinger]

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" <[e-mail]:

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" <[e-mail] in message

news:11**********************@v3g2000hsg.googlegro ups.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 -

[Chris Morley]

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

[Chris Morley]

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

[Allen Browne]

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" <mo*****@gmail.comwrote in message
news:11*********************@d55g2000hsg.googlegro ups.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