• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

SQL (mysql)- normalization advice - Best way to design clients / suppliers tables

Hi,
I'm building a new database in PHP / MySQL.

I have often wondered this and wanted to know what others think.

Lets say I'm building a "suppliers" table which holds information about a companies different suppliers.
I'm trying to decide if to build fields such as email address, and telephone numbers into the main suppliers table or whether to create a one to many relationship with a number of subtables.

In most cases suppliers will only have one email address or one main address but there are cases when they will have more.

therefore is it good practise or overkill to build the database table so in fact there are additional tables such as

"postal address"
"email_address"
"telephone_numbers"
all as seperate tables.

then I could even go further and use these tables for other entites such as "customers" etc.

Is this good advice or am I creating unnecessary complexity? is this just going to slow down my database performance and make coding harder as always having to think about linking the tables?

Thanks for any opinion.
0
afflik1923
Asked:
afflik1923
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>then I could even go further and use these tables for other entites such as "customers" etc.

that is indeed the "final" usage: that way, you don't have to code for addresses each time distinctly, but reuse the same component over and over again.
it's not only simplifying the db design, but also the coding later.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You can go further as you said.. You can go till Fifth Normalization.
The best practice is to go till third Normalization for better performance of queries.

I suggest you to stop in a single table itself but if your requirement can grow upon this, then you can go for separate tables.
0
 
reb73Commented:
It boils down to what is the projected size/usage of the database on an ongoing basis.. It is a good idea to have a normalized database schema and how the data contained within is projected to users and front-end applications/report clients is something that can be achieved by using views and efficient indexes.

But overkill can hurt too when the tables get bigger, in your case I believe it would be better to have a supplier table with common details that can be held in one record and to have a contacts table which will have a list of addresses/contact details segregated by type (Delivery Point / Billing Point / Finance etc)
0
 
dportasCommented:
If you have more than one set of contact details per contact then yes it does make sense always to create separate tables for those contact details. The Party Data Model is a common pattern for this scenario:
http://www.tdan.com/view-articles/5014

In general it's good practice to design a data model to be in at least BCNF or preferably 5NF. Compromise on that only if you have good reason to. In a SQL database the "good" reasons are usually either:

A) you need to enforce some constraint that can't otherwise be enforced effectively (because your DBMS doesn't support the necessary multi-table constraints) OR
B) you wish to effect some change in the underlying storage that isn't othewise possible (usually for performance reasons).
0
 
afflik1923Author Commented:
All good comments and a good link. Thank you for your contributions.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now