Would this be correct, if I do not break up the inventory table further?
[inventory] id (PK) [optionsList] id (PK) options [options] id (PK) optionList_id (FK_options_optionsList) inventory_id (FK_options_inventory) Douglas Brown Email: [EMAIL PROTECTED] ----- Original Message ----- From: "Bryan Stevenson" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, July 01, 2002 12:01 PM Subject: Re: DB layout questions...please help!!! > Hey Douglas, > > I'd break the INVENTORY table out some more.... > > VEHCILE_INVENTORY > Vehicle_ID > Make_ID > Model_ID > Year > > VEHICLE_MAKE > Make_ID > Make > > VEHICLE_MODEL > Model_ID > Model > > VEHICLE_OPTION > Option_ID > Option > > VEHICLE_INVENTORY_OPTION (1 vehicle_ID can have many option_IDs) > Vehicle_ID > Option_ID > > Now you're looking at doing some looping to add vehicle options: > > 1) Name the checkbox for ALL options the same (i.e. chkOptions) and give each box the value of it's > corresponding option_ID. This will pass a form var with that name containing a comma delimited list > of all the values for each option checked. > > 2) Your update logic: > -delete all exisiting options for the current vehicle > -add all the currently selected options: > <cfquery name="query" datasource="DSN"> > <cfloop list="#FORM.chkOptions#" index="Option_ID"> > INSERT INTO > Vehicle_Inventory_Option > ( > Vehicle_ID, > Option_ID > ) > VALUES > ( > #FORM.Vehicle_ID#, > #Option_ID# > ); > </cfloop> > </cfquery> > > Now if you were doing the initial adding of the vehicle and it's options...just skip the delete step > > HTH ;-) > > > > Bryan Stevenson B.Comm. > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > t. 250.920.8830 > e. [EMAIL PROTECTED] > > --------------------------------------------------------- > Macromedia Associate Partner > www.macromedia.com > --------------------------------------------------------- > Vancouver Island ColdFusion Users Group > Founder & Director > www.cfug-vancouverisland.com > > ----- Original Message ----- > From: "Douglas Brown" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, July 01, 2002 11:45 AM > Subject: DB layout questions...please help!!! > > > > I have an outomobile application to allow dealers to put their inventory > > online etc...but the car options, such as power steering,brakes,seats is > > set static on the pages and I want to make them dynamic and allow the > > dealer to be able to add additional opions if needed. The second > > question, is if the dealer adds options to the application, how do I > > dynamically modify the insert queries to be able to understand the new > > added field. As you can well tell, I am not a SQL guru and am confused > > when thinking in the fourth dimension. The layout I have so far is as > > follows. > > > > [inventory] > > id > > this table hold make, model, year etc.. > > > > [options] > > id > > inventory_id > > > > this table hold the options associated with the inventory... > > > > > > > > > > > > Douglas Brown > > Email: [EMAIL PROTECTED] > > > > > > > > > > Douglas Brown > > Email: [EMAIL PROTECTED] > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
