Hello,
Our company is currently redesigning its MySQL database to be more efficient. Right now, our major concern is how to effectively do multiple value lookups in one value table without sacrificing our searching capability. Basically, we are trying to link an "Item" from one table to multiple "Options" in another table without replicating too much data. Item 1 (lets say car1) has options 1, 2, 3 & 4 (doors, wheels, engine, sunroof) for example. Our current system (see below for an example) has a table in the middle that acts a reference table of sorts, which works alright, but we are currently doing two or more queries at run time; One to select and compile the multiple options into a single text string, and one to select the rest of the items info. Then we combine both queries and fire them out to a web page. What we would like to see is a single MySQL select query that joins the data for us into another string using the 'AS' name construct (not necessarily with the current database structure, we are open to all options at this point). If that is not possible (or recommended) any suggestions on how to improve & re-organize for efficiency is greatly appreciated. TIA, James. Example of existing system: Table item ------------ item_id - 1 item_desc - car1 Table item_TO_options ----------------------------- item_id option_id Data for Table item_TO_options ---------------------------------------- item_id | option_id 1....1 1... 2 1... 3 1....4 Table options ----------------- option_id option_desc
