Link to home
Start Free TrialLog in
Avatar of dimensionav
dimensionavFlag for Mexico

asked on

The best way to design a database model

We would like to know if our software architecture is reliable,  we have the following  tables:
INVOICES
ORDERS
QUOTATIONS

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?
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

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, About.com and a dozen other places.

One big mega table is just asking for trouble and doesn't meet any industry standard data modelling techniques.
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)
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...
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
+1 for Jim Dettman's comment!  Clear, concise and well explained.