Re: Opinions on this schema please =)

[Willem vanSchevikhoven]

Thank you for the input Peter and Rick!

The generic method is very important to me since what i have in mind is something like www.summitracing.com. They seem to have a lot of different properties which are all a part of the search for a suitable article. The problem with a set of columns directly on the product is that i just simply cant predict all the properties an item or article might have, we might get a new item which requires us to use a new type of property. off my head we could need at least a 100 columns for properties (rim offset, rim width, rim diameter, engine type etc) and we would only be using like 5 to 15 of them at a time.

The update speed is not one of my biggest concerns (or should it be?) as the main focus is to be able to provide a fast database for clients browsing products. The updates we make are not that important since the guys making an update are paid to do so. Clients that browse a slow website are most likely moving to an other shop since they have no strings attached.

I'm also have a fixation that tags should only be stored for one item or article at a time since storing two similar properties for an item and it's corresponding article might lead into big hassles if we fail to update the other tag too. Imagine inserting an alloy rim with the wrong offset, updating only one tag for the offset might lead to some very unpleasant situations if the client or our staffmember is misinformed by the database.

I could ofcourse take away the properties "master" table, and just have the item_properties and article_properties with an indexed property name. That would atleast get rid of two FROM (select ... join )'s.
This method will make managing tags a bit more messed up, but i thinki could live with it.:

SELECT DISTINCT II2A.item_id, II2A.article_id FROM
ITEM_PROPERTIES AS IP,

(SELECT I2A.item_id, I2A.article_id FROM
(SELECT ITEMS.item_id FROM ITEMS WHERE
ITEMS.item_price = 1890) AS I JOIN I2A
ON I2A.item_id = I.item_id) AS II2A,

ARTICLE_PROPERTIES AS AP

WHERE
(II2A.item_id = IP.item_id AND II2A.article_id = AP.article_id) AND
((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota')) AND

((IP.property_name = 'model' AND IP.property_value = 'supra') OR
(AP.property_name = 'model' AND AP.property_value = 'supra'));

If i could make a trigger to keep the item or article tags up to date for the item's and articles columns. (the price for example is a property that's a row on the item). The query could be written like:

SELECT DISTINCT I2A.item_id, I2A.article_id FROM

ITEM_PROPERTIES AS IP, I2A, ARTICLE_PROPERTIES AS AP

WHERE
(I2A.item_id = IP.item_id AND I2A.article_id = AP.article_id) AND

((IP.property_name = 'price' AND IP.property_value = '1980') OR
(AP.property_name = 'price' AND AP.property_value = '1980')) AND

((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota')) AND

((IP.property_name = 'model' AND IP.property_value = 'supra') OR
(AP.property_name = 'model' AND AP.property_value = 'supra'));

Am i on the right tracks by any chance? Or am i just hitting my head on the wall?