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