Re: [OT-ish] Optional Extras.

On Tue, 26 Jun 2001 05:23, Dave Mariner wrote: > Please excuse me if you consider this to be off-topic, but this is the > best place I can think of to ask the (slightly long-winded) question. > > Imagine you have a car database (MySQL driven). Different models have > different optional extras (air-con, central locking, immobiliser etc). > I need to store the optional extras in a searchable form - i.e. the > customer may have a wish-list of electric windows, aircon, and power > steering. However the optional extras list is not and will not be > finalised when the system goes live (probably will never be > finalised!). Therefore I cannot do the quick-and-dirty hack of putting > all the options as binary fields in my car database, so must come up > with a more elegant solution. I've thought of storing e.g. 10 tuples > car.option1->aircon code, car.option2->powersteering code. etc. and > also going down the header-detail route. > My current quandry is to which is going to be better for the search > aspect, considering I'd also like to give them a "best fit" option. > Would it be to create a cursor on my fixed criterion (price, age etc) > and then iterate through each of those manually in my php script (see - > it isn't entirely off topic ;0) ) counting the matches for that record > in the optional-extra detail table? Or would it be to do a select where > (optionalextra1=mychoice1 or optionalextra2 = mychoice1 ......) and > (optionalextra2=mychoice2 or optionalextra2 = mychoice2...... ) and > .... etc etc (yeuch!). > > I have a sneaking suspicion that there's a more elegant way than > either of these, but can't think of it at the moment. > > If you come up with the solution there's a beer in it for you the > next time you're in Paphos, Cyprus! > > Thanks in advance, > > Dave. Why not have a table that contains carid and optionlink, where carid is the identifier for a unique entry of car model and optionlink is ditto for a particular option. So you have something like Table car carid (autoincrement unique key) carname ...... Table options optid (autoincrement unique key) optdescription Table optlink carid optid Then you can do a select like select car.carid, options.description .... from car, options, optlink where car.carid = $whatever and car.carid = optlink.carid and (optlink.optid = x or optlink.optid = y ...) where you build the latter part from the list of options selected - maybe from a dropdown list that you can dynamically build from table options. I think I could have explained that better, but hopefully you get the idea. If you need, I think I have some example code somewhere. -- David Robley Techno-JoaT, Web Maintainer, Mail List Admin, etc CENTRE FOR INJURY STUDIES Flinders University, SOUTH AUSTRALIA "This is a sick bird," said Tom illegally.