The best way to design a database model

Posted on 2014-07-16
Last Modified: 2014-07-24
We would like to know if our software architecture is reliable,  we have the following  tables:

But we thought that maybe just one table called DOCUMENTS related with a DOCUMENT TYPE could be more simple,  but we are worried about the performance during queries and any other implication not seen yet.

What is your opinion?
Question by:dimensionav
    LVL 22

    Expert Comment

    by:Steve Wales
    I would consider it a much better design the way you have it.  Each entity in its own table.

    Using a lookup table analogy, if your database has a lot of lookup columns (state code, country code, job status, invoice status, the list goes on), do you have one lookup table (matching a code to a description) per code or one big mega table where there is a lookup code type, the code and then the description.

    Google "One True Lookup Table" - you'll find lots of articles out there on why it's considered a bad idea.

    The same would apply to your major database objects.  One table per object.  It makes queries easier to write (and more importantly, understand months or years down the road when you have to debug or alter them).

    You may also want to read up on Database Normalization.  Googling that just now returned articles at Wikipedia, Microsoft, and a dozen other places.

    One big mega table is just asking for trouble and doesn't meet any industry standard data modelling techniques.
    LVL 21

    Expert Comment

    by:Randy Poole
    Also with all 3 tables, I am assuming each could have more then one entry so you should have a table for each specifying the line item(s)
    LVL 22

    Expert Comment

    by:Steve Wales
    Expanding on that even more, there's probably more data you need.

    Orders needs a Vendors table where you keep you vendor information (who you order from, who you pay).  Invoices would also  use this table as reference, as would quotations, most likely.

    Each likely needs an items table as Randy indicated.

    Any time you go to store something that isn't directly related to the entity in the table, you're likely looking at another table.

    The list could go on and on...
    LVL 82

    Expert Comment

    by:Dave Baldwin
    Another that you are likely to need is an ORDERS-FILLED table that records the order as it was filled.  That table should Not update from any of the other tables like VENDOR because it should show what you have Done.  Addresses, descriptions, and similar items should never change once they are recorded in that table.
    LVL 107

    Expert Comment

    by:Ray Paseur
    Make a Google search for the exact phrase, "Should I Normalize My Database" and you'll find some very thoughtful writings on both sides of the question.  In your case, more tables are probably better than fewer tables.  I doubt that performance will be an issue when you're dealing with the kinds of row counts that can be generated by sales transactions.
    LVL 12

    Expert Comment

    You will also want to consider a table for order_line_items. Thinking about an order, you have the company info, shipping address, billing info, etc... That discribes the order "header". But then you may have multiple items in the order and you will want to have acces to just those values.
    LVL 47

    Expert Comment

    all 3 might have line items e.g.

    orders > order_items
    invoices > invoice_items
    quotations > quotation_items
    LVL 56

    Accepted Solution

    <<What is your opinion? >>

    One of the rules that you need to follow with relational DB's is that each record should have the same "shape" or more simply, all the fields in the table should all be about the same thing.   I'll give you an example.   Say we have a table that is for "Objects":

    tblObjects - One record per object.

     Now let's say I have two types of objects, cars and pens.   Both may have a make, model, and year, but a car has a VIN# and a pen has an ink color.  So now I have this:

    tblObjects - One record per object.

       The issue here is that I can never fill in a value for the VIN# for a pen, and a ink Color for a car.   It's not that I don't know these values when I create a record, but that I can never supply a value for that record.   So if you layout the fields horizontally,  you have:

              Description   Make   Model   Year    VIN   InkColor
    Car            x                    x          x           x          x
    Pen           x                    x          x           x                       x

     You can see the records are of a different shape.   That means I need two tables.   "Objects" is too generic.

     Now in your case, the difference between a "Quote", "Order", and "Invoice" may be nothing more that a single field indicating a status, in which case, you'd only need one table.   However they might also be three totally different things and might need three different tables.

     You'll most likely end up with three separate tables, but until you list out the facts (or attributes) for each, there's no way one can say which way is better.

    LVL 107

    Expert Comment

    by:Ray Paseur
    +1 for Jim Dettman's comment!  Clear, concise and well explained.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now