help with normalization of mysql database

Question

on my website a user can post comments and group these into categories.

eg.

category: good quotes
links: "some quote 1", "some quote 2",  and so on.

so the nr of categories and comments are determined by the users.

im thinking how i should organize this in the mysql database.

i cant have one table for each category because there would be A LOT of categories.

but how could i organize this in a table?

should a category be in one column, and a comment be a row? or should it be the other way around?

but then, isnt it a bad practice to increase the nr of columns after you have defined the table?

any suggestion would be helpful!

Answer 1

You could use something like (This is Sql Server but should give you an idea)

DECLARE @Categories TABLE(
        CategoryID INT,
        CategoryDescription VARCHAR(50)
)

DECLARE @Comments TABLE(
        CommentID INT,
        Comment VARCHAR(MAX),
        CategoryID INT,
        UserID INT
)

You could even maybe have specific categories per user, then change the categories table to

DECLARE @Categories TABLE(
        CategoryID INT,
        CategoryDescription VARCHAR(50),
        UserID INT
)

Which will allow you to specify user specific categories for their lookups on the UI.

You can take this design even further by craete a UserCategories table, which will allow multplie users to be linked to multiple categories.

This will almost be like a rules table.

DECLARE @UserCategories TABLE(
        UserID INT,
        CategoryID INT
)

and then keep the original Categories table as

DECLARE @Categories TABLE(
            CategoryID INT,
            CategoryDescription VARCHAR(50)
    )

Answer 2

You can create a table for categories and a table for comments (so one category is one row, and one comment is one row in their respective tables). You can also need another table with two columns -- an ID for category rows, and an ID for comment rows.

Then, to indicate that a comment belongs to some category, you add a row to the last table. For instance if a comment with ID 42 belongs to categories with IDs 6 and 8, you add the rows (42, 6) and (42, 8).

Answer 3

You'll need a Comment table and a Category table, so every comment and every category will be a row in their respective table.

Assuming a comment can belong to more than one category, you will also need a "bridge" table where each row will contain a comment id and the id of the category to which it belongs.

Answer 4

I would have distinct comments and categories, and then a 'linking' table to map relationships (and avoid duplication):

psuedo-sql:

comment { 
    id 
    comment 
}    
category { 
    id 
    category 
}    
comment_categories { 
    fk_comment_id  
    fk_category_id 
}