If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Setup & Normalization
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! |
#2
|
|||
|
|||
Table Setup & Normalization
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.) -- 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. "toolman74" wrote in message news 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! |
#3
|
|||
|
|||
Table Setup & Normalization
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 |
#4
|
|||
|
|||
Table Setup & Normalization
In message ,
toolman74 writes 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? 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. -- Bernard Peek 07790 444030 |
#5
|
|||
|
|||
Table Setup & Normalization
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!! "toolman74" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|