sql - Tagging database objects (string tags) and tag lookup -

multiple objects in our database need tagged string tags (completely arbitrary). 1 solution classic many-to-many relationship representation:

 table customer     customerid, customername   table product      productid, productname table tags         tagid, tagname table customertags customerid, tagid table producttags  productid, tagid 

another solution have xml column represents tags path secondary xml index improve sequential lookup:

 table customer     customerid, customername, tags table product      productid, productname, tags 

where tags xml column have tags <tags><tag name="tag1" /></tags> , path index /tags/tag

the first solution gives faster lookup, adds more tables. second solution slower cleaner.

i'm new sql , might have overlooked something, input highly appreciated.

my vote on first solution.

first of all, xml slower process on sql server 2008 equivalent straight tbl-bridge-tag setup. if wanted find products tagged x, conventional sql query start tag->product_tag->product. create xml indexes (as have mentioned), bulkier xml , need @ least 2 indexes per tag (one primary , 1 value - want value secondary index instead of path secondary index).

secondly, if rename/drop tag, have go through xml structure of each related object (product/customer) , use xml.modify (which has limited support - e.g. 1 node can modified @ time).