Database Normalization

Posted on 2011-10-03
Last Modified: 2012-05-12
I'm setting up a new database with the following types of orders

Purchase Orders (sent to manufacturer for inventory)
Sales Orders (received from customers)

The manufacturers will get reports of when/where their items are sold but I don't understand how I'll connect the two for reporting purposes

If I order 5 widgets for inventory at different costs over time - and 3 are sold - how do I know which widget went where....

Currently I have a field "SO_ID" on every "PO_ID" - and a "PO_ID" field on every "SO_ID" so when an item is picked and the serial number is captured they sync-up - but it just doesn't seem like the best way to do this.  It seems like the way I do it now has repeated data and that's what normalization tries to avoid.

Question by:ProdigyOne2k
    LVL 24

    Accepted Solution

    Think you should have a third table for inventories, which will contain both po_id and so_id.  Each widget will have a row on the table and will also act as the join table between purchases and sales.

    Author Comment


    Now when trying to re-do our database I'm trying to follow all of the "rules" I'm reading about...and something I read was that no column should be "null".  Maybe this isn't a *must* - but just wanted to check that out as well

    Of course the inventory will have a PO_ID right away - but the SO_ID would be "null" until it was committed to an order - is that a concern having a "null" SO_ID?
    LVL 24

    Expert Comment

    I don't know where you read that -> it's generally okay for a column to have null values unless it is the primary key.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now