There's a great deal of debate available, regarding tag schema's, but I have observed that many from it focuses on one content type, for example bookmarks or photos.

I am thinking about using tags across multiple options that come with a multi-tenant business application one where tags might be associated with form fields, documents, photos, configuration configurations and much more.

Let me design a more compact group of tables that may scale to those different needs, instead of eliminate link tables for every 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 employed to connect a variety of these products, but would connect a minimum of tags and tagType, at least. For example, a tag might be connected having a drop-lower area type. It might be a registry key having a registry type, a young child value and become connected by having an entity. A tag child may be another tag, to permit multi-level parent-child associations.

There is a risk with distribution, for the reason that many features become determined by a little group of tables.

If you have been challenged with a similar decision or for those who have a concept that will help, please share your ideas, approach, and just how performance pertains to the distribution risk.

Thanks!

I dont believe getting less tables can make things more effective. Isn't it easier to simply have separate tables for every content type. Its could be more readable. Queries to obtain counts would be also simpler and much more efficient getting less JOINs etc?

eg:

For Document:

DocumentTags Title CreatorID

DocumentMappedTags

For Photos:

PhotoTags Title CreatorID

PhotoMappedTags

So Mark has a good thing, but let us say you want to avoid multiple tags tables, and also the natural redundancy using the tags themselves. we're able to:

**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 have introduced a brand new problem - the Tags table is denormalized. In Mark's scenario as well as in my very own, here, we have introduced the generation of multiple tag names per tenant and creator, or of overloaded tenant and creator fields (multiple ID's in one record).

To repair that, we are able to:

  • change the entity and user context towards the TagMap tables, and join to a lot more than three tables. I believe that this is more effective than I organized during my initial publish because we have distributed this content.

    Produce a single Tags Table: Tags Title

    Leverage Tenant and User Tables Tenant Title, ... Customers Title, ...

    Documents: TagMap_Documents Documents

    Photos: TagMap_Photos Photos

  • change the entity and user context towards the content tables (documents, photos). The problem here would be that the tags are not entity or user specific, which could create noise in auto-completion/suggestion.

    Produce a single Tags Table: Tags Title

    Documents: TagMap_Documents Documents

    Photos: TagMap_Photos Photos

Searching for the silver bullet here, may need more thought compared to entire search ) Whether it wasn't only then do we would not be getting any fun then, anyway :)