Hi,

I just wanted to pick your brain about the best db design for vending
machine software.

One machine, has a 1 to many relationship with coils. Each coil can
only have a varying amount of max product depending on the product.

HOw would you track sales when over time coils may change and you want
to keep historical data,

For example jan coil 1 is a chocolate bar (max 5 items) then in feb
it's changed to a jumbo chocolate bar (max 3 items).

I was thinking

Machine
_______
id,
name,
(other important columns)

Coil
___
id,
machine_id,
machine_coil_id, (ex A1, A14)
product_id,
product max ( column for maximum number of items for this coil )
active ( used when the coil has the correct product id in it)

Sales
_____
id,
machine_id,
coil_id,
product_id,
item_price,
qty,
total_amount

Thanks for you input.