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.
Container will have a label, and is usually a child of a received order.
Currently the label has a bar code that contains the
Container represents AND the
RowID of the received record.
The question is what is best practice:
- Have one row per received Order that contains a
TotalProductQtyfor the ENTIRE received order and total Containers Recevied. Then have the labels on the Container have the
RowIDand that specific
ContainerQty. Then the Table is updated with a
UsedQtyColumn with the
Productthat is within the
Containerbeing used (based on the
ContainerQtyembedded in the barcode). Also, a column with
UsedContainersQty, but do not have any record in the system at a container level.
- Would I be better off to just enter each container into its own row and add a
UsedBool 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?
- I was also wonder if it would at all make sense to leave the
ReceivedOrdersTable as is with
ContainerQtythen create a new table for each container in a
ReceivedContainersTable that would have a foreign Key to the
ReceivedOrdersTable 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!