From a database design perspective, what you're talking about is a
many-to-many relationship. Typically, you would build this in your
database as a separate table that contains the primary keys of the two
parent (source) tables.
To provide an example for MS SQL Server 2000 using your situation,
you would create a new table:
CREATE TABLE Inventory_Options
( InvID int NOT NULL REFERENCES Inventory (ID),
OptID int NOT NULL REFERENCES Options (ID)
)
CONSTRAINT PK_Inventory_Options PRIMARY KEY (InvID, OptID)
If you have additional attributes that belong with the list of
options (e.g., quantity, quality, color) you would define those columns as
non-key columns of the Inventory_Options table. In your example, they
would probably be defined as nullable, since the attribute may not apply
to every option on every car. (For example, a quantity column doesn't
make much sense when you're talking about a truck's trailer hitch.)
When you add a new record to the Inventory table for the Toyota
Tacoma pickup truck that just came in, it gets ID 40. Then, the dealer
pulls up the web form and selects the options the truck came with
(populated from the Options table) and any applicable attributes for those
options (depending on how you defined the Inventory_Options table). When
they submit the form, assume they entered:
CD Player Stereo (id 4)
Hard bed cover (id 15)
Alloy wheels (id 7)
The Inventory_Options table now contains:
40 4
40 15
40 7
This makes your application scalable for any number of vehicles and any
number of options. However, be sure to cascade your deletions from the
Inventory table to the Inventory_Options table, or you'll end up with a
big mess.
Eric A. Laney
Systems Architect
Verizon Security
Voice: 813.987.1416
Pager: 888.551.3718
Possession, n.
The whole of the law.
"Douglas Brown" <[EMAIL PROTECTED]>
07/01/2002 02:08 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Help w/ relations
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 layout I have
so far is as follows. I imagine I need to add another table such as
[optionsList] but which will be the pri key table blah blah blah
[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]
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
