I'm implementing a "tags" features to an already working solution.
Final users need to be able to add tags to three separate sections of the solution:
Each section has its corresponding table in the database, and each table contains a unique ID:
I'm thinking about implementing a tag schema similar to the one that Wordpress uses, where I'll have:
tagmaptable that will contain a unique ID for each "tagmap".
- A foreign key to each section's table ID.
- Another foreign key to the ID of the tag.
CREATE TABLE Posts( PostID int(2) NOT NULL AUTO_INCREMENT, Content varchar(255), PRIMARY KEY(PostID) ); CREATE TABLE Groups( GroupID int(2) NOT NULL AUTO_INCREMENT, GroupName varchar(255), PRIMARY KEY(GroupID) ); CREATE TABLE Links( LinkID int(2) NOT NULL AUTO_INCREMENT, href varchar(255), PRIMARY KEY(LinkID) ); CREATE TABLE tagmap( TagmapID int(2) NOT NULL AUTO_INCREMENT, PostID int(2), GroupID int(2), LinkID int(2), TagID int(2) NOT NULL, PRIMARY KEY(TagmapID) ); CREATE TABLE tags( TagID int(2) NOT NULL AUTO_INCREMENT, TagName varchar(255) NOT NULL, PRIMARY KEY(TagID) );
I like this approach because it is decently normalized, but queries might get a bit complex. Also, it will have a lot of
NULL columns every time a tag is assigned to a post but not to an account or to a group, so I'm unsure how it will behave performance-wise (the table will hold around 100,000 records almost immediately).
Is there a better alternative to this?