On 6 Aug, 15:08, "bla...@worldweb.com" <bla...@worldweb.com> wrote:
> 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.

Why do you want to store the product max? And why do want to store it
with coils? Knowing nothing whatsoever about the vending industry, and
assuming that the price of any given item is assigned on a machine-by-
machine basis, I'd imagine a structure like this:



machine(machine_id*,machine_name,location)

eg

1,HAL2000,The Mall

machine_data(machine_id*,coil_id*,product_price_st art_date*,product_id,product_price)

eg

1,A14,2007-02-13,401879,1.50
2,A14,2007-02-14,401879,1.75

product(product_id*,product_name,coil_capacity)

eg

401879,Duff Beer,12

sales(machine_id,coil_id,sale_datetime)
HAL2000,A14,2007-02-15 17:05:27