The best way to design a database model

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
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.
Randy PooleCommented:
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)
Steve WalesSenior Database AdministratorCommented:
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...
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Dave BaldwinFixer of ProblemsCommented:
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.
Ray PaseurCommented:
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.
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.
all 3 might have line items e.g.

orders > order_items
invoices > invoice_items
quotations > quotation_items
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
+1 for Jim Dettman's comment!  Clear, concise and well explained.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.