CODEWITHSUNDEEP
sqlt-sqlsql-server-2008database-design
kateroh
kateroh

Reputation: 4416

Tagging database objects (string tags) and tag lookup

Multiple objects in our database need to be tagged by string tags (completely arbitrary). One solution is a 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 would be to have an XML column that represents tags with a PATH secondary XML index to improve sequential lookup:


table Customer     CustomerId, CustomerName, Tags
table Product      ProductId, ProductName, Tags

Where Tags is an XML column that would have tags like <tags><tag name="tag1" /></tags> and PATH index like /tags/tag

The first solution gives a faster lookup, but adds more tables. The second solution is slower but cleaner.

I'm new to SQL and might have overlooked something, so any input would be highly appreciated.

Upvotes: 1

Views: 335

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

My vote would be on the first solution.

First of all, XML is slower to process on SQL Server 2008 than the equivalent straight tbl-bridge-tag setup. If you wanted to find all products that are tagged X, a conventional SQL query will start from tag->product_tag->product. You could create XML indexes (as you have mentioned), but those are even bulkier than the XML themselves and you need at least two indexes per tag (one for primary and one for VALUE - you want a VALUE secondary index instead of a PATH secondary index).

Secondly, if you rename/drop a tag, you would have to go through the XML structure of each related object (product/customer) and use xml.modify (which has very limited support - e.g. only one node can be modified at a time).

Upvotes: 2

Related Questions