SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table Design check

    I know its pretty simple, but i want to get the design right, could someone point out what might be wrong (havent a chance to test it yet), what could be improved etc?

    Code:
    SET FOREIGN_KEY_CHECKS=0;
    
    -- Drop table Users
    DROP TABLE IF EXISTS `users`;
    
    CREATE TABLE `users` (
      user_id smallint(6) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
      username varchar(20) NOT NULL,
      password char(32) NOT NULL,
      email text NOT NULL,
      active set(0,1) NOT NULL DEFAULT '0,',
      age tinyint(4) UNSIGNED NOT NULL,
      forename varchar(50) NOT NULL,
      surname varchar(50) NOT NULL,
      
      PRIMARY KEY(`user_id`),
      INDEX `users_index`(`user_id`),
    ) 
    TYPE=InnoDB
    
    -- Drop table Images
    DROP TABLE IF EXISTS `images`;
    
    CREATE TABLE `images` (
      image_id smallint(6) NOT NULL AUTO_INCREMENT,
      user_id smallint(6) NOT NULL,  
      filepath text NOT NULL,
      description text NOT NULL,
      title text NOT NULL,
      
      PRIMARY KEY(`image_id`),
      INDEX `images_index`(`image_id`)
      FOREIGN KEY (`user_id`) REFERENCES users(`user_id`)
    )
    TYPE=InnoDB
    
    SET FOREIGN_KEY_CHECKS=1;
    I attatched a graphical version of the tables too.

    Thanks
    Last edited by Dangermouse; Jul 2, 2004 at 08:57.

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Do the filepath and title need to be TEXT fields? Could they be VARCHAR's? Same for email in the users table.

    Just a design thing that I usually follow is using singular terms for table names. So instead of 'users' I use 'user' and instead of 'images' I use 'image'.

  3. #3
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I too wasnt sure about text, i could use varchar. I used 'Users' because i see it as a collection of users, not just one user. thanks

    Does my FK look ok?
    Last edited by Dangermouse; Jul 2, 2004 at 08:56.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    TEXT is a 65k field, VARCHAR would be better (max is 255) for filepath, description, title, email

    your FK is fine

    i'm not completely sure if innodb require explicit second indexes, but i don't think so, and i think the indexes you've declared on the PKs are redundant (PKs are unique indexes)

    plural table names is my preference too

    be careful with names like users, might duplicate a system table name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the feedback Rudy,

    This is the sql im using now (i switched to another db design program)

    Code:
    CREATE TABLE artist_users (
      user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      username VARCHAR(20) NOT NULL,
      password CHAR(32) NOT NULL,
      email VARCHAR(60) NOT NULL,
      forename VARCHAR(80) NOT NULL,
      surname VARCHAR(80) NOT NULL,
      age TINYINT UNSIGNED NULL,
      PRIMARY KEY(user_id)
    );
    
    CREATE TABLE artist_images (
      image_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      users_user_id MEDIUMINT UNSIGNED NOT NULL,
      fullpath VARCHAR(200) NOT NULL,
      title VARCHAR(100) NOT NULL,
      description TINYTEXT NOT NULL,
      PRIMARY KEY(image_id, users_user_id),
      INDEX artist_images_FKIndex1(users_user_id),
      FOREIGN KEY(users_user_id)
        REFERENCES users(user_id)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION
    )
    AUTO_INCREMENT = 1;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    very nice

    what's the tool that created the diagram?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its called 'DBDesigner 4', not very user friendly but a good program. You can synch with remote/local databases, create images of your db, create html reports, add relationships, reverse enginer a db, lots of stuff. Open source too - http://www.fabforce.net/dbdesigner4/

    The other one i used previously was called 'MicroOLAP Database Designer for MySQL'. Not free though


  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks, good to know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •