PHP/MySQL - Adding 'tagging' system to ecommerce system?

Dave!

Hey folks,

The company I work for has an ecommerce system, and it has a pretty basic categorisation system which is essentially a table of products, and a table of categories, and the products fall into one or many categories - pretty straight-forward really.

Now we're hoping to give the the store owner a bit more control over how things are categorised, and make it easier for the customer to search for things.

So we're looking at adding tags to products, ie. shoe, red, female = women's red shoes!

I'm struggling at the moment to think of what's the best way to go about it... What DB schema would be best?

Let's assume the products table just has:

-product_id
-product_name

Would it be best to add another field (tags), with a list of tags seperated by a space?

Or would I (presumably) be better off creating a new table (tags) like:

-tag_id
-product_id
-tag_group
-tag

(maybe have a Tag_Group table then to serve the purpose of the current Categories table)

But if I do the above, then I'm struggling to figure out how I'd perform the search... The idea would be to select multiple tags using checkboxes, so would I have to take the list of tags, stick them together in a way that could be used in another query (red, shoe, female), and then perform the second query to get a list of product_id's, and then do the same with them to actually search for the products?

I'm sure I'm missing some potential JOIN somewhere

I would have thought the earlier option of another field in the product table would be best, because then I could just use:

SELECT * FROM product
WHERE tags LIKE '% $tag %'

...or some variation of that for multiple tags...

Any thoughts appreciated! Get my creative juices flowing

Background: I'm a junior developer in a very small company, and I've only really been playing with PHP/MySQL for 6 months or so.

JimmyCrackCorn

1:

Tag(TagID,Tagname)
MapTagProduct(TagID,productId,TagGroupIdentifier)

A little more complicated

2:

Tag(TagID,Tagname1, tagName2,tagname3,tagname4)
MapTagProduct(TagID,productId)

limited number of tags

ocallagh

I'd go with the first option recommended by JimmyCracked ie:

tags - tags_products - products

This way it's easy to manage the tags, delete/rename etc.

The alternative option is to just dump them all in one field in the products table as you suggest and stick a full text index on it. This might offer quicker searching (depends on the number of products/tags you're talking about), but then management of the tags is an absolute nightmare going forward.

A third option would be a combo of the two (not sure how good this is in terms of database design though)

You could use the first suggestion for management of tags (tags - tags_products - products) but to speed things up when querying the data, you could create an additional field in products called tags (with full text index on it) which would be populated with the space separated tags from the tags_products and tags tables. A cron could re-populate this field once a day or so.

Dave!

Thanks guys...

So going with the tags - tags_products - products approach, could you suggest how I might perform a search with a dynamic number of tags (using checkboxes as I mentioned)?

ie. I want the user to be able to select shoe, red, female, and then return a list of products that match all of these tags!

Dunno why but I'm struggling to think of a way :-/

ocallagh

tags - {id, name}
tags_products - {id, tag_id, product_id}
products - {id, name, etc}

You just need to join them up then. The query below might be slow enough and I'm sure could be improved on. I have a very similar setup with users, users_roles and roles. Query below takes about 0.14 seconds for 13,000 users and 5,000 roles. With Mysql query cache turned on the site should be fine as long as the data is not constantly changing.

SELECT products.* 
FROM   tags_products 
       INNER JOIN tags 
         ON tags_products.tag_id = tags.id 
       INNER JOIN products 
         ON products.id = tags_products.product_id 
WHERE  tags.name IN ( 'shoe', 'red' ) 
GROUP  BY tags_products.product_id;

Have a look here for more on this technique (actually this website is exactly what you are looking for, it has many more ideas about tagging): http://forge.mysql.com/wiki/TagSchema#Toxi

ocallagh

Actually - just reading that article - it's very good, if you're going to go for tags, tags_products and proeucts then here is the recommended table layout for the 3 table system (many-to-many relationship):

http://forge.mysql.com/wiki/TagSchema#Recommended_Architecture

paul159

The above mentioned way is good to manage the transactions. The best software would be the one which will help your way in increasing the profits of your business. The shopping cart software is gaining good importance as they help people in getting best deals for the products they buy online.