[SOLVED] INSERT......ON DUPLICATE question

[asmith3006]

Hi,

 

I'm having trouble getting my head around INSERT...... ON DUPLICATE at the moment.

 

I have three tables,

 

Product details:

id, description.....

 

Categories

type, value

 

Product Categories

product_id, category_value

 

Example data for the Categories table:

height, short

height, tall

colour, red

colour, blue.

 

What I want to do is have no more than one entry for each category 'type' for each product (i.e. a product can be tall and red but not tall and short).

 

I was hoping to do this with INSERT...ON DUPLICATE as this means when a user changes a product from red to blue I don't have to do "if colour exists delete colour insert new colour".

 

Thanks for any and all help!

[Mchl]

INSERT ... ON DUPLICATE KEY won't help you here. You might want to take a look into triggers.

[asmith3006]

Triggers... look promising. Can you give me a quick pointer please? I'm reading the documentation now but finding it a bit confusing.

[Mchl]

You should create a trigger BEFORE INSERT to Product Categories table. Inside you should check, if you should insert or update.

 

Alternatively, you could do a small change to your table design

 

Product Categories
product_id, category, category_value
1, 'height', 'tall'
1, 'color', 'red'
2, 'color', 'blue'

 

and create a unique key on (product_id, category)

then you could use INSERT ... ON DUPLICATE KEY UPDATE

[asmith3006]

I think I like the sound of the duplicate key more (I know how to do them!)

 

Which is 'better' from a database point of view?

[Mchl]

The simpler, the better.

Use UNIQUE key.

[asmith3006]

Will do. Thanks!