[thelist] MySQL Table Structure

[Andrew Maynes]

I am using MySQL for the web DB :)

I asked about making relationships between an Item in one table to another but
more importantly making a relationship to more than one field in the other
table...

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.  So how is item1 to
know which 4 colours from a table with 20 records in are related to it?

I created the two tables suggested

CREATE TABLE `cw_colourItem` (
`cw_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Item_ID` INT NOT NULL ,
`Colour_ID` INT( 25 ) NOT NULL
);

CREATE TABLE `Ref_Colours` (
`ColorID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`ColorName` VARCHAR( 25 ) NOT NULL
);

I understand the relationship part of this it is the storing of more than one
colour that is teasing me?  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?

Andrew