Clever Geek Handbook

SQL database structure for custom categories

I am creating an online blog site and for each blog post, I would like the user to be able to create / edit / delete their own category so that they can categorize their post.

What is generally considered good database design for user generated categories?

This is my suggested table design. (Is there a name for this db type?)

USER_TABLE
user_id (pk), user_name

CATEGORY_TABLE
category_id (pk), category_name

USER_CATEGORIES
user_id (fk), category_id (fk)

Thanks for helping. I'm sure there is a message in there somewhere, but I couldn't find it. If this is cheating, please let me know and I will delete this question.

+2


source to share


3 answers


This is the relationship of many to many. This would allow each user to potentially have many different categories, and each category could potentially have many different users. This seems like a useful model for what you are trying to do.



+5


source


I think your circuit looks good. You keep the category labels in one table to avoid duplication, then just assign your IDs to the users.



+2


source


If what you are trying to do is have "private" categories for each user, then that's okay. If, on the other hand, the categories are supposed to be public (sth like tags on stackoverflow), then you might consider another option - not to keep the user category and lt> relationship, instead add a use_counter field to the category table and use triggers to increment it when category (blog post is categorized) or decrement when "freed" (blog post deleted / its category deleted). When use_counter reaches 0, remove the category.

+1


source






More articles:


All Articles