Choosing tables for one-to-many relationships in MySQL

Question

I was wondering if I allowed my users to pick which categories their post will be displayed in and they can pick multiple categories from one to many should my MySQL tables look something like the tables below? If not what should I add or delete?

Here is my MySQL tables.

CREATE TABLE users_categories (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   category_id  INT UNSIGNED NOT NULL,
   post_id INT UNSIGNED NOT NULL,
   user_id INT UNSIGNED NOT NULL,
   date_created DATETIME NOT NULL,
   PRIMARY KEY (id)
);

CREATE TABLE categories ( 
   id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
   parent_id INT UNSIGNED NOT NULL DEFAULT 0, 
   category VARCHAR(255) NOT NULL, 
   url VARCHAR(255) NOT NULL,
   PRIMARY KEY (id), 
   INDEX parent (parent_id),
   UNIQUE KEY(parent_id, url)
);

CREATE TABLE users (
   user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   username VARCHAR(255) DEFAULT NULL,
   pass CHAR(40) NOT NULL,
   PRIMARY KEY (user_id)
);

CREATE TABLE users_posts (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   user_id INT UNSIGNED NOT NULL,
   title TEXT NOT NULL,
   content TEXT DEFAULT NULL,
   PRIMARY KEY (id)
);

Answer 1

• user_id in users_categories is a duplicate; the post is already associated with the user, and the category is associated with the post. Hence, transitively, a connection already exists between the post and the user.

• users_categories does not need an id as it is a mapping table, and should be named posts_categories.

• users_categories field date_created is unlikely to be useful and is probably a mistake.

• users_posts should probably be named posts.

• Use foreign key constraints where applicable.

The rest looks fine.

Answer 2

If I understand correctly, you may want to consider renaming users_categories to posts_categories and getting rid of the user_id column:

CREATE TABLE users_categories (
   category_id  INT UNSIGNED NOT NULL,
   post_id INT UNSIGNED NOT NULL,
   date_created DATETIME NOT NULL,
   PRIMARY KEY (category_id, post_id)
);

In addition note that you may also want to use a composite primary key on (category_id, post_id) instead of using a surrogate key. This primary key can uniquely identify each relationship, and in addition will guarantee that no post is linked to the same category more than once.