I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By tagging I mean that we wish to allow the user from the front end to define tags for these entities (keywords), something like Facebook tagging. We would like to make it extensible for any future entity that may appear later in the system and we want it to be taggable too. Initially we thought that this could be a many to many relation ship between each entity that we want it to be taggable and each tagged entity in the database. For example, if we want to tag users in photos, then many users can be tagged in a single photo and many photos may have the same user tagged into them. But this design complicate things because we will need many to many relationship between all the taggable entities and the objects that we want to add tags to. So we will have a many to many relationship between user and photos, user and video, .... and this is only for the user entity, if we have other entity we will do the same, so it's complex and non extensible. So we tried to enhance that and we came with the following idea.
We will have a table with no relationships at all, it's not connected to any other table, this table will contain the following, the tag id, tag value, the entity object id (ex: user id), the entity object type (ex: user), the taggable object id (ex: photo id), the taggable object type (ex: photo). and we will add indexes for entity object id column and entity object type, also for taggable object id and taggable object type to fasten the search process.
But I was wondering if there any pattern for that, some design pattern that solves this issue, and which of the above 2 solutions is better.