Hello everyone,
I have a database design as follows:
CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
password CHAR(40) NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(40) NOT NULL,
acct_type VARCHAR(20) NOT NULL,
company VARCHAR(40) NOT NULL,
email VARCHAR(80) NOT NULL,
reg_date DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE(username),
INDEX(password, username),
UNIQUE(email)
);
CREATE TABLE messages (
-> message_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> forum_id TINYINT UNSIGNED NOT NULL,
-> parent_id INT UNSIGNED NOT NULL,
-> user_id MEDIUMINT UNSIGNED NOT NULL,
-> subject VARCHAR(100) NOT NULL,
-> body LONGTEXT NOT NULL,
-> date_entered TIMESTAMP NOT NULL,
-> PRIMARY KEY (message_id)
-> );
CREATE TABLE images (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
filename VARCHAR(255) NOT NULL,
mimetype VARCHAR(50) NOT NULL,
description VARCHAR(255) NOT NULL,
filedata MEDIUMBLOB
);
CREATE TABLE user_images (
user_id MEDIUMINT(8) UNSIGNED NOT NULL,
id INT(11) NOT NULL,
PRIMARY KEY (user_id, id)
);
CREATE TABLE forums (
forum_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60) UNIQUE(name)
);
The point here is:
- user_id in the users table is linked to the messages table and user_images table.
- ‘id’ in the user_images table is linked to the images table
- messages table is linked to the forums tables using the forum_id
NB: The user_images table is ment to be a look up table so that when a user with
acct_ type designer is logged into the site, he can upload images via this user_images table.
Secondly, parent ID is used to track a reply to the same subject in the messages table.
Summary:
What I am trying to design is a database where users(who are designers) register into my website whose details are stored in the users table and the login to upload images onto the website.
I am not sure if my design is right and the main issue is how to upload images into the database once a user is logged in.
Thanks.
ncax2