blaine@worldweb.com wrote:
> On Aug 6, 10:20 am, strawberry <zac.ca...@gmail.com> wrote:
> > 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
>
> I wanted to store it by product max as each coil can only have a
> certain amount of product. This would also require a re-stocking level
> column that was missed.

Well I think the structure I've proposed might satisfy your
requirements. Play around with it and see where it gets you.