GUID vs Identity for use with tags

Question

I know that this issue was discussed in detail (I read a lot of posts here and around the Internet), and I don’t like digging a dead horse, but I have a question about a more specific aspect of Integer ID vs GUID:

I am writing a schema that will have tagging capabilities similar to those used for stackoverflow tags, but it will have the same tags used for 5+ different tables.

The main tables that I will link are the following:

Tag table

Tag ID Tag Name Tag Description ------------------------------------------------------------- 1 Hats Tag for hats 2 Coats Tag for coats 3 Gloves Gloves tag 4 Ladies Ladies item 

Elements Table 1

 Item ID Item Name Cost ------------------------------------------------------------ 1 Deerstalker £20.00 2 Fedora £50.00 3 Scarf £15.00 

I have a problem with the tag_item table.

I will have 5 tables with completely different structures that I want users to apply tags to, so I think I need to do one of the following:

• Save the table name / table number , as well as the integer key of the row to which the tag belongs
• Save the string GUID , this will work regardless of the table and make it easier to get all the tags for a specific row.

I am not sure how this will affect performance for:

• Search all items in 5 tables with a specific tag / tags
• editing tags for an item
• joins

Is there any clearer option in this case, or where could I read the benefits of this particular scenario?

---

Edit:

For everyone who is interested, below is the diagram on which I decided:

Table for tags

 CREATE TABLE [dbo].[Sys_TagList]( [Sys_Tag_Primary] [int] IDENTITY(1,1) NOT NULL, [Sys_Tag_Name] [varchar](50) NOT NULL, [Sys_Tag_Description] [varchar](1000) NULL ) 

Table for Tag_Items

 CREATE TABLE [dbo].[Meta_Tags]( [Met_Tag_Primary] [int] IDENTITY(1,1) NOT NULL, [Met_Tag_Link_FK] [int] NOT NULL, -- Link to [Sys_Tag_Primary] field [Met_Tag_Name] [varchar](25) NOT NULL, -- To enable tag searches to use a single table [Met_Tag_Table] [varchar](25) NOT NULL, -- Table name, used with [Met_Tag_RowID] to create link to that item [Met_Tag_RowID] [int] NOT NULL -- Primary key of item[n] table ) 

Answer 1

I'm going to (possibly useless) to suggest that the GUID versus ID is the wrong question to focus on. Assuming you need one table linking tags to elements, it might be worth exploring first.

If your five product categories are different enough to require different tables, you should probably consider five tag_item link tables - one for each element table. Although the code that queries them all at once (using UNION ALL to concatenate the results) may be a little more verbose and repetitive than the code for a single link table, the queries are likely to be much simpler — good both from the service and perspective of the query plan.

Another alternative would be to refactor common attributes, which five categories of elements are divided into one central table (to which tags are attached), and store additional non-common attributes in five child tables. It is difficult to find out if this is suitable if there is no additional information about how the existing data is used.

Answer 2

Be sure to duplicate tag design

Depending on how you intend to use tags and what performance characteristics are important, one of the possible alternatives to a map / link table is a hash combination table.

Calculate the hash of the requested tag combination and save it directly in existing tables and in the combination table.

A request for something that is a subset of tags may start to get hacked, although as you finish doing a lot in the memory transforms of all these hashes. There may be a cleaner way to request hashed reference values, since I can't remember this, and haven't found anything in the search.