[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