Multi-Tenant Tag Schema's for Distributed Content Types

Question

There's quite a bit of discussion out there, regarding tag schema's, but I've noticed that most of it focuses on a single content type, such as bookmarks or photos.

I'm interested in using tags across multiple features of a multi-tenant business app; one where tags could be related to form fields, documents, photos, configuration settings and more.

I'd like to design a smaller set of tables that can scale to these different needs, rather than stamp out link tables for each content type, which adds some complexity:

tags {
  tagsID
  tagName
}
tagChildren {
  childID
  childValue
}
tagType {
  typeID
  typeName
}
entity {
  entityID
  entityName
  ... 
}
tagMap {
  mapID  
  tagsID (FK)
  childID (FK)
  typeID (FK)
  entityID (FK)
}

The tagMap could be used to connect any number of these items, but would connect at least tags and tagType, at a minimum. For instance, a tag may be associated with a drop-down field type. It may be a registry key with a registry type, a child value and be associated with an entity. A tag child might be another tag, to allow for multi-level parent-child relationships.

There's a risk with distribution, in that many features become dependent on a small set of tables.

If you've been challenged by a similar decision or if you have an idea that would help, please share your thoughts, approach, and how performance relates to the distribution risk.

Thanks!

Answer 1

I dont believe having less tables will make things more efficient. Is it not better to just have separate tables for each content type. Its would be more readable. Queries to get counts would also be easier and more efficient having less JOINs etc?

eg:

For Document:

DocumentTags { ID TenantID Name CreatorID }

DocumentMappedTags {ID DocumentID DocumentTagID }

For Photos:

PhotoTags { ID TenantID Name CreatorID }

PhotoMappedTags {ID PhotoID PhotoTagID }

Answer 2

So Mark has a good point, but let's say we want to avoid multiple tags tables, and the inherent redundancy with the tags themselves. we could:

**Create a single Tags Table:**
  Tags { TagsID, TenantID, Name, CreatorID }    

**Documents:**
  TagMap_Documents { TagMap_DocumentsID, DocID, TagID }
  Documents { DocID, Location/Blob, ... }

**Photos:**
  TagMap_Photos { TagMap_PhotosID, PhotoID, TagID }
  Photos { PhotoID, URL, PhotoBlob ... }

Now we've introduced a new issue - the Tags table is denormalized. In Mark's scenario and in my own, here, we've introduced the generation of multiple tag names per tenant and creator, or of overloaded tenant and creator fields (multiple ID's in a single record).

To fix that, we can:

• shift the entity and user context to the TagMap tables, and join to more than three tables. I think that this would be more efficient than what I laid out in my initial post because we've distributed the content.

  Create a single Tags Table: Tags { TagsID, Name }

  Leverage Tenant and User Tables Tenant { TenantID, Name, ... } Users { UserID, Name, ... }

  Documents: TagMap_Documents { TagMap_DocumentsID, DocID, TagID, TenantID, CreatorID } Documents { DocID, Location/Blob, private(bit), ... }

  Photos: TagMap_Photos { TagMap_PhotosID, PhotoID, TagID, TenantID, CreatorID } Photos { PhotoID, URL, PhotoBlob, private(bit), ... }

• shift the entity and user context to the content tables (documents, photos). The issue here is that the tags themselves are not entity or user specific, which can create noise in auto-completion/suggestion.

  Create a single Tags Table: Tags { TagsID, Name }

  Documents: TagMap_Documents { TagMap_DocumentsID, DocID, TagID } Documents { DocID, Location/Blob, TenantID, CreatorID, private(bit), ... }

  Photos: TagMap_Photos { TagMap_PhotosID, PhotoID, TagID } Photos { PhotoID, URL, PhotoBlob, TenantID, CreatorID, private(bit), ... }

Looking for the silver bullet here, might require more thought than the entire hunt ;) If it wasn't then we wouldn't be having any fun then, anyway :)