I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.
Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.
Using a schema similar to the one below the
VariationId values, along with any future entities, would be generated using a
SEQUENCE (using SQL Server 2012) and each generated value would be stored in the
Entity table along with its
The use of the
SEQUENCE across multiple entities would allow for a single
Attachment table and therefore a
Attachment could be created against a
Variation and any future entities.
Entity table is used as a sort of junction table (or some other term) and would allow queries to return all
Attachments against particular entity type(s).
Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.
Am I going down the right path or should this be avoided.