Page 1 of 1

tricky problem

Posted: Sun Dec 19, 2004 5:20 pm
by sakaveli
this is kinda hard to explain, but i'll try.

im trying to make a database for alloy wheels. the price of these wheels will be linked to the size of the alloy i.e there might be one type of alloy wheel in 6 different sizes. so one type of alloy will have 6 different prices.

at the moment my database holds ONE price for ONE stock item. but now i can hold X amount of prices for one stock!

if it helps, my DB has an item table where all general item info is held i.e. price, description, picture etc. then each stock item is held in its own table, i.e tyres table, alloys table, etc. where more item specific information is held.

the problem is, i dont quiet know how im going to get around this problem without restructuring my entire DB! any ideas guys? :oops: :?

Posted: Sun Dec 19, 2004 5:28 pm
by rehfeld
your going to have to restructure the db, but barely

just add multiple prices for each product

17" wheel = $100
18" wheel = $200

you will no longer be able to retrieve a price without specifying the size of the wheel
the just fetch the price by specifying the product, where the wheel size = 17 or whatever

Re: tricky problem

Posted: Sun Dec 19, 2004 5:38 pm
by npeelman
sakaveli wrote:this is kinda hard to explain, but i'll try.

im trying to make a database for alloy wheels. the price of these wheels will be linked to the size of the alloy i.e there might be one type of alloy wheel in 6 different sizes. so one type of alloy will have 6 different prices.

at the moment my database holds ONE price for ONE stock item. but now i can hold X amount of prices for one stock!

if it helps, my DB has an item table where all general item info is held i.e. price, description, picture etc. then each stock item is held in its own table, i.e tyres table, alloys table, etc. where more item specific information is held.

the problem is, i dont quiet know how im going to get around this problem without restructuring my entire DB! any ideas guys? :oops: :?
Why would different size alloys be grouped under 1 stock number? ALTER your database so that each alloy has a different stock number:

Alloy #12345
15" -> 12345-15
16" -> 12345-16
17" -> 12345-17

Then you could do extra queries like keeping inventory of actual sizes available/onhand/searches, etc.

Norm

Posted: Sun Dec 19, 2004 6:34 pm
by McGruff
You've got a one wheel type to many sizes relationship. Create a second table with sizes, linked to the wheel type type table on an id field. More info here: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf.