[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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?
0
dimensionav
Asked:
dimensionav
1 Solution
 
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, 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.
0
 
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)
0
 
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...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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.
0
 
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.
0
 
North2AlaskaCommented:
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.
0
 
PortletPaulCommented:
all 3 might have line items e.g.

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

 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.
Description
Make
Model
Year
VIN#
InkColor

   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.

HTH,
Jim.
0
 
Ray PaseurCommented:
+1 for Jim Dettman's comment!  Clear, concise and well explained.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now