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

Posted on 2009-02-08
Last Modified: 2013-12-07
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"
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.
Question by:afflik1923
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >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.
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    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.
    LVL 25

    Assisted Solution

    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)
    LVL 22

    Accepted Solution

    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:

    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).

    Author Closing Comment

    All good comments and a good link. Thank you for your contributions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now