[thelist] MySQL Table Structure
rudy
r937 at interlog.com
Mon Jan 27 15:21:01 CST 2003
> this was based on colours for one item in another table. so Item 1
> could have 4 colours from the table colours associated with that colour.
>
> CREATE TABLE `cw_colourItem` (
> `cw_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
> `Item_ID` INT NOT NULL ,
> `Colour_ID` INT( 25 ) NOT NULL
> );
yes, that's a "relationship" table
by the way, you do not need the cw_ID key in that table --
create table ItemColor
( ItemID integer not null
, ColorID integer not null
, primary key (ItemID, ColorID)
, foreign key (ItemID) references Items (ItemID)
, foreign key (ColorID) references Colors (colorID)
)
storing of more than one colour is easy, each item/colour combination is a
separate row
sample data --
Items
301 bicycle
302 wheelbarrow
303 parachute
Colors
1 red
2 green
3 blue
4 gold
ItemColor
301 1
301 3
302 2
302 3
303 1
303 4
what colour(s) does the parachute come in?
> Also when I create a new item and want to update this item
> I was thinking of using a tick box to assign the colours to the item.
> This doesn't sound too easy to do or am I just blinkered?
tick box? you mean checkbox? in my opinion, a better choice is
<select multiple>
where all the options are generated from the Colors table
<option value="#ColorID#">#ColorName#</option>
(please change coldfusion octothorp syntax to whatever you're using)
rudy
More information about the thelist
mailing list