[g18c]

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/fields in.

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 from the customers and suppliers tables?

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 fields (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

[r937]

we've had this discussion on this forum before

unfortunately it is not easy to find these threads, because people use such illuminating thread titles as "please help me with my ERD" and "database design considerations" and "a data modelling conundrum" which means that as you scroll down the list of previous threads, you don't really know what they're about

and i will not suggest using this site's search function because it's b0rked

so perhaps we will need another thread -- note that this one has a nice title

Originally Posted by g18c

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 from the customers and suppliers tables?

more sense? no, but it is a practical alternative

Originally Posted by g18c

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 fields (such as state) then i
would need to add that for each table that has address like fields.

how you can anticipate forgetting the state column is a puzzle

Originally Posted by g18c

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?

that's certainly a good option

[Frunkie]

I mysqlf have asked that very same question because it seems like the best way to go. One address table for the entire database, one telephone number table etc. The problem I had was that I took it too far.. I wanted 1 table for names, one table for phone numbers, etc. My model was an absolute nightmare and it was really hard to follow because of all the association tables that were needed.

If your model is small, I wouldn't think it would be a big deal. When you model your tables like that, you will see the real "fun" begin when you have to join everything back together with all of the association tables that are needed.

[Pat Phelan]

I actually created a separate address table in one of the largest databases that I designed from scratch.

The developers hated that at first because it meant that they had to code one extra join for every address that they needed. They soon discovered that it meant that they could build one "address pannel" and simply pass it the appropriate address_id and get the display/edit of any address out of the way with one line of code.

For me as a DBA it was even better, since all of the addresses were in one table... Any time we needed to make changes (such as zip code refreshes, mailing route updates, etc) there was only one place to hunt the little beggars down. This was a huge boon to me!

I see data modeling and object modeling as closely related. If you can factor out a component object from your object model, then I think that you ought to factor it out from your data model too.

-PatP

[g18c]

Ok thanks so much for the replies. On the flip side, i use the CSLA .NET framework for my business objects tier and that, rightly so, has the methodology of modeling the business objects as behavioural and not as derived classes per se. This does make for more repetitive coding (or code generation) as classes will share attributes but will be explicitly defined in each class, but it does mean the structure is well defined and easy to follow at the expense of bloat.

I think from what people have said i will need to find a middle ground, perhaps i will have the following as it will fit well:

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

and so on, basically having the one address table and having independent tables hooking these addresses to the required tables, customers, suppliers, employees etc. Does that sound sensible?

Really appreciate the help, i know some db design but im more software engineer than DBA! If i can help anyone with business objects or winforms front end code ill be glad to help.

Cheers,

Chris

[Frunkie]

I think that looks like a great design. I would be curious to see what both r937 and Pat think of it. I have questions of my own I would like to ask.

[r937]

it works for me

[Pat Phelan]

I'm rather pragmatic... If it works for Chris, it works for me. The design itself looks quite workable to me.

I'm hoping that the CustomerID in the EmployeeAddresses was supposed to have been an EmployeeID though!

-PatP

[Frunkie]

Chris,

What did you decide for the other entities? Did you get them yet?

[blindman]

You have suppliers and customers in separate tables.
Can a supplier never be a customer, or vice versa?
You should combine both these tables into a single table called "Businesses", which would eliminate your duplicate address problem, and maybe other problems as well.

[g18c]

Thanks for the replies chaps. Yes the CustomerID was a mistake, should be EmployeeID. I havent finished the schema design just yet as i was side tracked with getting a new server installed But the comments are very welcome and will help me to progress as i have new ideas and will post shortly.... i want to keep the ideas rolling as i have had many since people kindly gave me pointers, its been a great help.

One of these ideas is what blindman has said, yes combining customers and suppliers into a generic 'contacts' table would make sense. However, sometimes different entities will require different fields. For instance, people in my company, suppliers and customers are all people correct? However, for our employees we would want to store passport number, salary etc, additional fields we wouldnt want to store (or we wouldnt even know!) from suppliers or customers... what do we do in theses cases? Do we have the one contacts table and end up with some of the fields as null, or do we have seperate tables with the fields as required in each (hence the rational to abstract out the address table as these would be common between all people entities). For my case, an employee would never be a customer nor a supplier and vice versa. I have seen http://allenbrowne.com/AppHuman.html which would be quite interesting but i am aware of keeping it simple as possible as it will make my business logic and objects easier to develop.

Appreciate any comments on my thoughts above.

Cheers,

Chris

[g18c]

Further to my last post, you could have a ContactsTable, and if that contact is an Employee rather than having a roles list you could have a EmployeeContacts table which would have the additional employee fields such as passport, salary etc:

Contacts
ContactID_PK
Name
Address (or include seperate address table)
Phone

EmployeeContacts (1:1 join on Contacts.ContactID_PK to EmployeeContacts.ContactID_PK)
ContactID_PK
Passport
Salary

By definition, if a ContactsID exists in the EmployeeContacts table (1:1 join) they are be default considered an employee.

Is this normal scenario for using 1:1 joins enabling the user to subclass and divide data? I did read 1:1 joins are not very common?

The only trouble i can see is that the more data is spread across tables like the above scenario the more business logic that will need to be entered to ensure that the database remains in a valid state (i.e. EmployeeContacts must have an entry in the Contacts table which we can do by enforcing referential integrity).

Thanks,

Chris

[blindman]

Originally Posted by g18c

...However, for our employees we would want to store passport number, salary etc, additional fields we wouldnt want to store (or we wouldnt even know!) from suppliers or customers... what do we do in theses cases?...

Unless there are scads of Employee-specific fields, just leave these null. Avoid 1:1 joins. They are a relic of a time when disk space was expensive and processing power was weak.

[pootle flump]

scads: http://acronyms.thefreedictionary.com/scads
???

[Pat Phelan]

Scads.

-PatP