Table Setup & Normalization

  • Thread starter Thread starter toolman74
  • Start date Start date
T

toolman74

I recently got the suggestion to create a Contacts table which would have ALL
names & addresses in it - whether it's our employee, our vendor, or our
client. I'm a little confused because reviewing the Northwind sample they
show the address, city, etc. fields in the suppliers table, the employees
table and the customers table. Is this sample not normalized? Also, if I do
this, what would be my best approach for handling the sales reps for our
clients whom all have their own separate contact #s and such?

Thanks so much!
 
Where ever you have multiple tables that have similar fields, ask yourself
the question whether these should be one table.

To answer your question, it's not strictly a question of whether the data is
normalized or not. Northwind is normalized, but having separate tables means
it is limited. You would have trouble extending it to handle financial data
as well (receipts and payments made.) Because they don't have everyone in
the same table, you can't just create a Transactions table with a foreign
key that handles payments to everyone (employees, vendors, shippers, refunds
for customers) and from everyone. The same is true for any other related
tables you might need (e.g. appointments or meetings that include any
combination of staff, customers, etc.) As a result, it is almost always a
better design to put everyone in the one table.

Regardless of whether you use one table for your staff and customers or not,
you need related tables to handle further data. Part of the skill of db
design is to spot where the one-to-many relationships may be. The simplest
possible design that copes with every situation that will arise -- that's
the goal, and it takes some experience to get it.

Examples:
a) Is one address per client enough? Or do you need to record different
kinds of address (street, postal, business, home, past....) Same for phone
numbers.

b) In assigning sales reps to clients, is ONE sales rep per client enough?
Or do you need to record different sales rep over time (historical data), or
even at one time (e.g. part-time sales reps, or where one is mentoring
another.)
 
Where ever you have multiple tables that have similar fields, ask yourself
the question whether these should be one table.

To answer your question, it's not strictly a question of whether the data is
normalized or not. Northwind is normalized, but having separate tables means
it is limited. You would have trouble extending it to handle financial data
as well (receipts and payments made.) Because they don't have everyone in
the same table, you can't just create a Transactions table with a foreign
key that handles payments to everyone (employees, vendors, shippers, refunds
for customers) and from everyone. The same is true for any other related
tables you might need (e.g. appointments or meetings that include any
combination of staff, customers, etc.) As a result, it is almost always a
better design to put everyone in the one table.

Regardless of whether you use one table for your staff and customers or not,
you need related tables to handle further data. Part of the skill of db
design is to spot where the one-to-many relationships may be. The simplest
possible design that copes with every situation that will arise -- that's
the goal, and it takes some experience to get it.

Examples:
a) Is one address per client enough? Or do you need to record different
kinds of address (street, postal, business, home, past....) Same for phone
numbers.

b) In assigning sales reps to clients, is ONE sales rep per client enough?
Or do you need to record different sales rep over time (historical data), or
even at one time (e.g. part-time sales reps, or where one is mentoring
another.)
 
Hi,

in most contact modules I made or worked with all contacts are in 1 table.
Then you have the table with contact types (customer, vendor, ....), and a
third table to combine the two, because there exist situations where a
certain contact can be of different types, for example I made an application
for a book store, so it's a customer, but I also buy my books there, so it's
also a vendor.

success
NG
 
Hi,

in most contact modules I made or worked with all contacts are in 1 table.
Then you have the table with contact types (customer, vendor, ....), and a
third table to combine the two, because there exist situations where a
certain contact can be of different types, for example I made an application
for a book store, so it's a customer, but I also buy my books there, so it's
also a vendor.

success
NG
 
In message said:
I recently got the suggestion to create a Contacts table which would have ALL
names & addresses in it - whether it's our employee, our vendor, or our
client. I'm a little confused because reviewing the Northwind sample they
show the address, city, etc. fields in the suppliers table, the employees
table and the customers table. Is this sample not normalized? Also, if I do
this, what would be my best approach for handling the sales reps for our
clients whom all have their own separate contact #s and such?
Click to expand...

Either approach is valid, which is better is entirely dependent on how
you plan to use the data. Is there a valid business reason to put all of
your contacts in a single table? Do you have a need to list all of your
contacts in a single report? If the answers to these questions are no
then your database will probably be simpler using separate tables.

You could legitimately build a logical entity relationship diagram which
includes employees and vendors as children of a single parent. In
general it's best to keep the physical structure of tables as close as
possible to the logical structure of entities. But there are sometimes
legitimate reasons for making the two different. As long as you
understand the costs of doing that it is OK to do it. There are usually
multiple different ways of implementing any given logical data
structure. Because of that there will always be an element of art within
the science of data modelling.
 
In message said:
I recently got the suggestion to create a Contacts table which would have ALL
names & addresses in it - whether it's our employee, our vendor, or our
client. I'm a little confused because reviewing the Northwind sample they
show the address, city, etc. fields in the suppliers table, the employees
table and the customers table. Is this sample not normalized? Also, if I do
this, what would be my best approach for handling the sales reps for our
clients whom all have their own separate contact #s and such?
Click to expand...

Either approach is valid, which is better is entirely dependent on how
you plan to use the data. Is there a valid business reason to put all of
your contacts in a single table? Do you have a need to list all of your
contacts in a single report? If the answers to these questions are no
then your database will probably be simpler using separate tables.

You could legitimately build a logical entity relationship diagram which
includes employees and vendors as children of a single parent. In
general it's best to keep the physical structure of tables as close as
possible to the logical structure of entities. But there are sometimes
legitimate reasons for making the two different. As long as you
understand the costs of doing that it is OK to do it. There are usually
multiple different ways of implementing any given logical data
structure. Because of that there will always be an element of art within
the science of data modelling.
 
Thanks for the clarification but it just leads me back to the question of the
sales reps which would have a one-to-many relationship with the respective
vendors. Is it a limiting design to have a separate sales rep table with
their names and phone #s? I want to keep the design as unlimiting as
possible for the future. Thanks!!
 
Thanks for the clarification but it just leads me back to the question of the
sales reps which would have a one-to-many relationship with the respective
vendors. Is it a limiting design to have a separate sales rep table with
their names and phone #s? I want to keep the design as unlimiting as
possible for the future. Thanks!!
 
Back
Top