My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the database for an application I'll be coding.
The situation: I've got Reports in one table and Recommendations in another table. Each Report can have many Recommendations. I also have a separate table for Keywords (to implement tagging). However, I want to have just one set of keywords that gets applied to both Reports and Recommendations so that searching on keywords gives you Reports and Recommendations as results.
Here's the structure I started out with:
Reports
----------
ReportID
ReportName
Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)
Keywords
----------
KeywordID
KeywordName
ObjectKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)
RecommendationID (foreign key)
Instinctively, I feel like this isn't optimal and that I should have my taggable objects inherit from a common parent, and have that comment parent be tagged, which would give the following structure:
BaseObjects
----------
ObjectID (primary key)
ObjectType
Reports
----------
ObjectID_Report (foreign key)
ReportName
Recommendations
----------
ObjectID_Recommendation (foreign key)
RecommendationName
ObjectID_Report (foreign key)
Keywords
----------
KeywordID (primary key)
KeywordName
ObjectKeywords
----------
ObjectID (foreign key)
KeywordID (foreign key)
Should I go with this second structure? Am I missing any important concerns here? Also, if I do go with the second, what should I use as a non-generic name to replace "Object"?
Update:
I'm using SQL Server for this project. It's an internal application with a small number of non-concurrent users, so I don't anticipate a high load. In terms of usage, the keywords will likely be used sparingly. It's pretty much just for statistical reporting purposes. In that sense, whatever solution I go with will probably only affect any developers that will need to maintain this system down the line... but I figured it's good to implement good practices whenever I can. Thanks for all the insight!