Best approach for old and new sales orders - one table or multiple?

[khayward]

I've looked around a bit for this topic and ended up in the weeds looking at other items. I do like to follow advice and search for an answer before asking.

So, I have thought about this a few days and can't seem to figure out the best approach. I'm working on adding sales orders to my developing project and I'm wondering whether to use one table or more than one. I don't know how I'd implement more than one but it has been suggested that it might be useful when there are many records to have closed sales orders on a separate table from open sales orders.
My gut says it would be better to have just the one and have a 'Status' field or 'Open/Closed' yes/no field. The data I'm working with has a fair few dates in it also. I guess the same question applies to the data I have for purchase orders too.

So, is it better to have one table and have the information filtered by status or multiple tables?

[ranman256]

1 tbl for Order
1 tbl for OrderItems (child of tOrder)

tOrder.Status is open/closed
or instead just use a tOrder.DatePaid field. (open = null, closed has a date)

[orange]

khayward,

I don't know that there is a single definitive answer. Probably depends on your business and rules more than some principle.

Can you tell us about a day in the life of your SalesOrders business? A brief scenario involving the other things involved - customers/clients, purchase, invoice, item/product-- whatever you deal with.
What makes a SalesOrder complete (Open/Closed)?

Here's a link to a generic data model from Barry Williams' site dealing with Sales, Orders, Products, Customers

Good luck.

[khayward]

I'll try to describe the business process as best I can. The background is that we are a contract manufacturer - Our customers design wire harnesses and cable assemblies and contract us to manufacture them. The process of a sales order goes something like this (I've tried to capitalize the important objects and entities):

- A sales order is received from a customer
-- A Sales Order can have one or more Assemblies
-- Each Sales Order is assessed for correct Pricing, Assembly Revision and feasibility of the Customer required Due Date
--- Each Assembly on the Sales Order is added to a Schedule tab on a Spreadsheet, each Assembly has a master record that is copied from another tab on that Spreadsheet
--- Information unique to each Sales Order is added to cells to the right of information copied from the master record
---- One Assembly may have several Unit Prices depending on the Order Quantity (This will be represented with a junction table to get the Many-to-Many aspect functional)

- Once the Assemblies have been added to the Spreadsheet a Work Order is issued, a folder for the relevant Assembly is pulled from files and the Work Order is passed on to the Kitting stage, where Materials are issued to that Work Order and then any 'cut-to-length' Materials are processed and the Work Order is passed to Production for assembly.

- Once the work order has been assembled, tested and inspected it is passed to Shipping for packaging and shipping. At this time the Quantity Completed and Date Shipped fields in the Spreadsheet are filled out.

- Quickbooks is used to manage the financial aspect
-- Quickbooks is used to generate a Packing Slip for the Sales Order, or portion thereof being Shipped
-- Once the Sales Order has been fulfilled Quickbooks is used to generate an Invoice which is then sent to the Customer

I've also attached a partial screenshot of the Spreadsheet 'Schedule Tab' with the fields - obviously I can't share what's in them.

Uses of the system I am working on will include generating an Open Order Report filtered/ordered by due date, perhaps a Report that also shows Work Orders that are complete and need to be Invoiced. One other aspect is that I will be trying out using a junction table to create a Materials List/BOM for each Assembly, so I will want to have a way for the system to report materials requirements but that's another topic.

khayward,

I don't know that there is a single definitive answer. Probably depends on your business and rules more than some principle.

Can you tell us about a day in the life of your SalesOrders business? A brief scenario involving the other things involved - customers/clients, purchase, invoice, item/product-- whatever you deal with.
What makes a SalesOrder complete (Open/Closed)?

Here's a link to a generic data model from Barry Williams' site dealing with Sales, Orders, Products, Customers

Good luck.