Having Problems with Databaase Design

Databases
1

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:

  1. user_id in the users table is linked to the messages table and user_images table.
  2. ‘id’ in the user_images table is linked to the images table
  3. 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

2

You don’t need the user_images table because the relationship between users and images is one-to-many, not many-to-many. Put a user_id column in the images table.

3

As described, the user_images table is unnecessary, it forces an extra join that is not needed. just put the user id into the images table

An image belongs to just one user, so the relation is user–<image
not user–<user_images—image the last bit being a one to one relation

EDIT Oh, Dan can type faster than me…

4

Thanks for the tip.
@Dr John

I want a one to many relationship between user and image. Thant is a user can upload more than one image. Will this still hold after removing the user-images table?
Thanks

I have a second issue please:
Say after a user is logged in, in the logged in script; where user can upload images; which of these queries will work:

$uploadfile = $_FILES[‘upload’][‘tmp_name’];
$uploadname = $_FILES[‘upload’][‘name’];
$uploadtype = $_FILES[‘upload’][‘type’];
$uploaddesc = $_POST[‘desc’];

		//	Open file or binary reading ('rb')
		$tempfile = fopen($uploadfile, 'rb');
	
	
		//	Read the entire file into memory using PHP's
		//	filesize fucntion to get the size.
		$filedata = fread($tempfile, filesize($uploadfile));
	
		//	Prepare for database insert by adding backslashes
		//	before special characters.
		$filedata = addslashes($filedata);
	
	
		//	Create the SQL query.
		$sql = "INSERT INTO images SET 
				filename = '$uploadname', 
				mimetype = '$uploadtype',
				description = '$uploaddesc',
				filedata = '$filedata' 
                                    WHERE user_id.users = user_id.images";
			
		//	Perform the insert.
		$ok = @mysql_query($sql);

Given that I have added user_id in the images table.

Once again thanks for the help.

5

that’s not going to work

you need to develop and test your SQL outside of php before you include it in your app

please note we have a separate forum for mysql

:slight_smile:

6

You need to populate that user_id column like the other columns in your INSERT query. INSERT queries do not have WHERE clauses.

Also, you cannot use addslashes() to escape variables for a MySQL query. You must use mysql_real_escape_string(). There are other things than single quotes that need to be escaped, especially when inserting binary data as you’re doing.

7

Yes it will.