CODEWITHSUNDEEP
mysqldatabase
ajsie
ajsie

Reputation: 79676

Normalization of mysql database

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

e.g.

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

So the number of categories and comments are determined by the users.

I'm thinking how I should organize this in the mysql database.

I can't 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 number of columns after you have defined the table?

Any suggestion would be helpful!

Upvotes: 0

Views: 133

Answers (4)

reech
reech

Reputation: 340

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 
}

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166336

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)
    )

Upvotes: 0

Matteo Riva
Matteo Riva

Reputation: 25060

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.

Upvotes: 0

Michael Williamson
Michael Williamson

Reputation: 11438

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).

Upvotes: 1

Related Questions