SEO

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.

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

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.

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.

On 6 Aug, 23:14, strawberry <zac.ca...@gmail.com> wrote:
> bla...@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.

For instance, based upon my structure (I think) a query like this
would itemise all sales...

SELECT m.machine_name, m.location, a.coil_id, `start` , `end` ,
p.product_name, a.product_price, s.sale_datetime, p.coil_capacity
FROM (

SELECT md1.machine_id, md1.coil_id, md1.product_price_start_date
'start', md2.product_price_start_date 'end', md1.product_id,
md1.product_price
FROM machine_data md1
LEFT JOIN machine_data md2 ON md2.machine_id = md1.machine_id
AND md2.coil_id = md2.coil_id
AND md2.product_price_start_date > md1.product_price_start_date
GROUP BY 1 , 2, 3
ORDER BY `START` , `END`
)a
INNER JOIN sale s ON s.machine_id = a.machine_id
AND s.coil_id = a.coil_id
AND s.sale_datetime
BETWEEN a.`start`
AND a.`end`
LEFT JOIN product p ON a.product_id = p.product_id
LEFT JOIN machine m ON a.machine_id = m.machine_id

I think this could be written without recourse to a subquery - but it
was a bit easier this way.