Database Design (Inventory DB)

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...).

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.