Database Design (Inventory DB)

问题:

I'm looking to design an inventory database that tracks a snack bar. As this would be a single person/computer access and need to be easily movable to another system, I plan to use SQLite as the DB engine. The basic concept is to track inventory bought from a wholesale warehouse such as Sams Club, and then keep track of the inventory.

The main obstacle I'm trying to overcome is how to track bulk vs individual items in the products database. For example if a bulk item is purchased, let us say a 24 pack of coke, how do I maintain in the product database, the bulk item and that it contains 24 of the individual items. The solution would be fairly easy if all bulk items only contained multiple of 1 item, but in variety packs, such as a carton of chips that contains 5 different individual items all with separate UPCs, the solution becomes a bit more difficult.

So far I have come up with the multiple pass approach where the DB would be scanned multiple times to obtain all of the information.

Product_Table
SKU: INT
Name: TEXT
Brand: TEXT
PurchasePrice: REAL
UPC: BIGINT
DESC: TEXT
BULK: BOOLEAN
BulkList: TEXT // comma separated list of SKUs for each individual item
BulkQty: TEXT // comma separated list corresponding to each SKU above representing the quantity

Transaction_Table
SKU: INT
Qty: INT
// Other stuff but that is the essential

When I add a bulk item to the inventory (A Positive Quantity Transaction), it should instead add all of it's individual items, as I can't think of any time I would keep in stock to sell the bulk item. I would like to keep the bulk items in the database however, to help receiving and adding them into the inventory.

回答1:

one way to do it is to create a 1:N mapping between bulk objects and their contents:

create table bulk_item (
  bulk_product_id integer not null,
  item_product_id integer not null,
  qty integer not null,
  primary key(bulk_product_id, item_product_id),
  foreign key(bulk_product_id) references product(sku),
  foreign key(item_product_id) references product(sku)
);

A comma-separated list is certainly fine (it might make it harder to do certain queries such as find all bulk objects that contain this SKU etc...).

回答2:

I have to both agree and disagree with jspcal. I agree with the "bulk_item" table, but I would not say that it's "fine" to use a comma separated list. I suspect that they were only being polite and would not endorse a design that isn't in first normal form.

The design that jspcal has suggested is commonly called "Bill of Materials" and is the only sane way to approach a problem like composite products.

In order to use this effectively with your transaction table, you should include a transaction type code along with the SKU and quantity. There are different reasons why your stock in any given SKU might go up or down. The most common are receiving new stock and customers buying stock. However, there are other things like manual inventory adjustments to take into consideration clerical errors and shrinkage. There are also stock conversions like when you decide to bust up a variety pack into individual products for sale. Don't think you can count on whether the quantity is positive or negative to give you enough information to be able to make sense of your inventory levels and how (and why) they've changed.