?
Solved

Application design - One table or Many?

Posted on 2007-08-06
9
Medium Priority
?
214 Views
Last Modified: 2013-11-29
Application design / methodology.

I am looking for opinions on 'how best' to accomplish this. The module that I am working on is basically a Quote/SalesOrder/Inventory process.
The table relevant to this question is the Parts table.  Each part needs to be 'tracked' through various stages.  A part will 'progress' through QuotedPart,ConfirmedSalePart, PurchaseOrderedPart, Received Part, WarehouseLocationPart, OutboundTruckPart, DeliveredToCustomerPart,ProcessCompletedOnlyNeedHistoryPart.
Additional identifying information is 'attached' to the part at each stage.  eg SalesOrderNumber, PO Number,ShipDate,ReceiveDate,WarehouseLocation, etc.

The basic question is this:

Should I have MULTIPLE tables, 'moving' the parts OUT of a table INTO the next level table OR
Should I have ONE table of all parts with flags/indicators of 'where' the part is in the process?

Considerations:
This is a networked environment where 40 or 50 people may be involved in processing, queries, etc. for any and all the parts at any stage.
There are also many processes, updates, queries and procedures associated with each stage which are only concerned with that stage.
At any given time, there will be 100,000 'active' part records.
Once the part goes through its 2-3 month life cycle, it will become 'static', no additional processing, but it must be available to query.
In a year or 2, there would be millions of 'no longer of interest' parts (having completed their cycle).

This will be an Access front end on SQL 2005 database tables.
 
0
Comment
Question by:Volibrawl
  • 4
  • 4
9 Comments
 
LVL 6

Expert Comment

by:gjutras
ID: 19640472
Here's a quick article that might help with why
http://www.ibm.com/developerworks/web/library/wa-dbdsgn1.html
and
http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html

but you should be breaking your table up in to logical entities reflected in seperate tables with foriegn keys tieing them together with constraints to make sure there is data integrity.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19640919
A Customer (CustID) in the PO with multiple items (parts?), price, warranty, etc.??  Don't quite have the whole picture yet.
0
 
LVL 6

Expert Comment

by:gjutras
ID: 19640972
In your case with what you've said so far it sounds like you have a part (I'd call it an order item in my dictionary) and the part has a current status.  You can represent the status with one column in the parts table. You might want to have an enum or a lookup type table to contain what the statuses are.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 6

Expert Comment

by:gjutras
ID: 19640982
as for the additional information, it belongs to the part so it can be in that one table with nulls until the information gets filled in.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19642702
Volibrawl?
0
 
LVL 9

Author Comment

by:Volibrawl
ID: 19644979
Hi guys,

Thanks for the insights ...  I don't think the question pertains as much to normalization as it does to performance issues.  I know about keys and relationships, that is not my quandry.  This is not merely a case of flagging the 'status' of an 'order item' (good term for what I am dealing with).

Here is an example of what is going on:  A salesperson 'assembles' an order and presents a quote.  During that phase of the process, there are maybe 100 quotes, each with 100 order items, different pricing structures, discounts, terms being negotiated, etc.  A lot of analysis, approval, revision, collation, printing, review is being done by (say) 20 network users.  They want to 'see' QUOTES only.  The quotes change over time, some are trashed, some languish into oblivion and others turn into bonafide orders.  Each quote has (at least) a header table and an QUOTED ITEMS TABLE.  The items table contains (at least) the part numbers, qty, confirmed price for each item.

This is the first phase .. later, the items will have evolved from QUOTED ITEMS to SALES ORDER ITEMS to PURCHASE ORDER ITEMS to INBOUND RECEIPTS to
INVENTORYinaspecificLOCATION to OUTBOUNDITEMS to DELIVERYSTOPITEMS to COMPLETEDORDERUNDERWARRANTY, etc.

Here is the substance of my question:  When the quote is confirmed as an ORDER.  Should I just change the Status of 'THE ITEMS' to ORDER or I should I move the quoted items to a different table (SALES ORDER ITEMS).  Recognize that a different 20 users will be doing a lot of analysis, processing, etc. on the OPEN SALES ORDER ITEMS only.  They have no interest in the 100,000 QUOTE ITEMS in that table nor in the 2,000,000 COMPLETEDORDERITEMS in that table.  I am concerned that the performance will be severely degraded if I am always dealing with tables of millions of 'ITEM' records.  I am thinking it would be better to keep the 'active' records of each 'phase' in their own tables.  When the item achieves a certain 'status' I move it to the next table.  The final table (COMPLETED) would be the HUGE table, but there would never be any updating or changing to those records, they would only be queried.

I am pretty much leaning toward separate tables.  Are there any dissenting opinions?  Are there any suggestions for easily migrating the items from table to table?
0
 
LVL 6

Accepted Solution

by:
gjutras earned 1000 total points
ID: 19645070
with the performance worries you're talking about.  I would seperate them in to seperate tables.  I'd also even make an archive set of tables, where archive versions of the tables exist, where you can move items to when they hit a status that says they no longer really needed.  If you needed to query against all for a report, just use a view to combine your online tables and your archive tables.  Also, look in to putting the archive tables in to a seperate filegroup that lives on a different harddrive than the main database.
With all this moving(insert in one place, delete in a another), your transaction logs will grow fast, so you'll need to make sure you have a good backup schedule in place so the transaction logs can naturally shrink to needed sizes after backups.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 1000 total points
ID: 19647166
If you never have to have the 'big picture', then separate tables might be ok.  One way might be to maintain the one large table, and use make table queries to extract the portion that is of interest to a select few.  When they are finished, you could use an update query to amend the selected records.

Can you think of any occasion where anyone might need the 'big picture'.  I've found that letting Sales keep everything until a sale is confirmed is a good place to divide.  Once you open a Sales order and subsequent Purchase Orders the data can be separated from Sales.  However, at some point, someone is going to ask: "How much did we think it was going to cost, how much did it cost, what did we sell it for, were there any schedule penalties, warranty costs, how much did we make"?

Keep in mind what you need to answer that question when you are deciding to go multi-tables.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19647817
Thanks, glad to help.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

864 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