T-SQL tag database database design?
Scenario
I am creating a database containing a number of different tables. They consist of a COMMENT table, a BLOGS table, and an ARTICLES table. I want to be able to add new elements to each table and tag them with tags 0 to 5 to help the user more accurately identify specific information.
Initial thoughts for architecture
My first thoughts were to have a centralized TAGS table. This table will list all available tags using the TagID field and TagName field. Since each item can have many tags, and each tag can have many items, I would need MANY MANY relationships between each item table and TAGS table.
For example:
Many COMMENTS may have many TAGS. Many TAGS may have a lot of COMMENTS.
Many ARTICLES may have many TAGS. Many TAGS can have many ARTICLES.
etc.....
Current understanding
From previous experience I understand that the way to implement this structure in T-SQL is to have a join table between the COMMENTS table and the TAG table. This merged table will contain the CommentID and TagID, as well as its own unique CommentTagID. This structure also applies to all other elements.
Questions
First, is this the correct way to implement such a database architecture? If not, what other methods would be feasible? Since the database will eventually contain a lot of information, I need to make sure it is scalable. Is this a scalable implementation? If I had a lot of these tables, would this architecture make CRUD operations very slow? Should I use GUIDs or Incrementing INTs for ID fields?
Help and suggestions would be greatly appreciated.
Thankyou.
source to share
You can also look at the WordPress schema and database description to see how others have solved a similar problem.
source to share
Keeping a centralized tag table is a good idea if you ever need to do one of the following:
- Create a complete list of all tags (which mix blog tags, comment tags, and article tags)
- Update tags so that they update everywhere: so that when changed
sqlserver
to,sql-server
it changes anywhere: in blogs, articles and comments.
- Update tags so that they update everywhere: so that when changed
This option is 1
very useful for creating tag clouds, so I recommend creating a tag table and linking to it from your tables.
Unless you need to update tags as described in option 2, you will never need a surrogate key for them.
In any case, you will most likely need a limitation UNIQUE
, and it makes no sense not to do so PRIMARY KEY
if you are not going to update them.
It will also save you a lot of connections: you don't need to join the tags table to show the tags.
GUIDs
are easier to manage, but they make indexes and link tables quite large in size.
You can assign a numeric ID to each table and create a link like this:
tTag (tag VARCHAR(30) NOT NULL PRIMARY KEY)
tTaggable (type INT NOT NULL, id INT NOT NULL, PRIMARY KEY (type, id))
tTagLink (
tag VARCHAR(30) NOT NULL FOREIGN KEY REFERENCES tTag,
type INT NOT NULL, id INT NOT NULL,
PRIMARY KEY (tag, type, id),
FOREIGN KEY (type, id) REFERENCES tTaggable
)
tBlog (
id INT NOT NULL PRIMARY KEY,
type INT NOT NULL, CHECK(type = 1),
FOREIGN KEY (type, id) REFERENCES tTaggable,
…)
tArticle (
id INT NOT NULL,
blog INT NOT NULL FOREIGN KEY REFERENCES tBlog,
type INT NOT NULL, CHECK(type = 2),
FOREIGN KEY (type, id) REFERENCES tTaggable,
…)
tComment (
id INT NOT NULL PRIMARY KEY,
article INT NOT NULL FOREIGN KEY REFERENCES tArticle,
type INT NOT NULL, CHECK(type = 3),
FOREIGN KEY (type, id) REFERENCES tTaggable,
…)
Please note that if you want to delete a blog, article or comment, you must also delete from tTaggable
.
Therefore, it tTaggable
is only used to enforce referential integrity. To request all tags for an article, you simply ask this request:
SELECT tag
FROM tTagLink
WHERE type = 2
AND id = 1234567
so you get all the tags by querying one table, without any connections.
source to share
usually the many-to-many relationship is implemented exactly as you describe it.
Auto-incrementing IDs are a good idea as it ensures that they are unique.
And you can use hints if you want to tag comments and articles with the same tag (instead of 6 tables, you only need 5). But searching with hints can be slower.
source to share