Application design - One table or Many?
Posted on 2007-08-06
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?
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.