Storing lists is a bad idea. One major reason is the database cannot properly control data integrity if you store lists of IDs. Best to build it properly the first time, because data changes after the fact SUCK ;-)
Now I see my original explanation: > > VEHICLE_INVENTORY_OPTION (1 vehicle_ID can have many option_IDs) > > Vehicle_ID > > Option_ID may not have been clear... I meant 1 vehicle can have many options. That means multiple records in the VEHICLE_INVENTORY_OPTION table and not a list of options in 1 record. Hope that clears that up 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 12:24 PM Subject: Re: DB layout questions...please help!!! > 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] > > > > > > > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
