Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Not 100% sure how to explain this situation but here it goes:

I am building an Inventory Database that all items received are entered into and a label is created for each Container, that is going to have bar code to tie the physical items to the records in the database.

So, every Container will have a label, and is usually a child of a received order.

Currently the label has a bar code that contains the Qty of Product that Container represents AND the RowID of the received record.

The question is what is best practice:

  1. Have one row per received Order that contains a TotalProductQty for the ENTIRE received order and total Containers Recevied. Then have the labels on the Container have the RowID and that specific ContainerQty. Then the Table is updated with a UsedQty Column with the Qty of the Product that is within the Container being used (based on the ContainerQty embedded in the barcode). Also, a column with UsedContainersQty, but do not have any record in the system at a container level.
  2. Would I be better off to just enter each container into its own row and add a Used Bool column? This would clearly make quicker updates, but would also make the table MUCH larger and would (I would think) slow down the inserts and possible effect performance with selects because a received order will then go from 1 insert/Row To select to anywhere from 5 to 50?
  3. I was also wonder if it would at all make sense to leave the ReceivedOrders Table as is with TotalProductQty and ProductID and ContainerQty then create a new table for each container in a ReceivedContainers Table that would have a foreign Key to the ReceivedOrders Table and a row for each container that can be tied to the labels.

These are the best options I can think of implementing but I don't have the slightest clue as to what one will give the best performance?

Any help is GREATLY appreciated!

share|improve this question
    
Code each way, preferably with varying implementations. Turn on Profiler for your SPID, on the SQL:BatchCompleted and RPC:Completed events, and look at the Reads, Writes, CPU, and Duration columns. Run each variant a few times; compare Profiler results. Then you'll know how each performs with your particular data in your particular environment. Then take the best and tune further to improve it as required based on how often it'll run and how much it takes to run. –  Anti-weakpasswords Apr 2 at 6:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.