SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Having Problems with Databaase Design

    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ncax2 View Post
    $sql = "INSERT INTO images SET
    filename = '$uploadname',
    mimetype = '$uploadtype',
    description = '$uploaddesc',
    filedata = '$filedata'
    WHERE user_id.users = user_id.images";
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ncax2 View Post
    // 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.
    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. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ncax2 View Post
    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
    Yes it will.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •