Table Design check

[Dangermouse]

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?

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

[Hartmann]

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'.

[Dangermouse]

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?

[r937]

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

[Dangermouse]

Thanks for the feedback Rudy,

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

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;

[r937]

very nice

what's the tool that created the diagram?

[Dangermouse]

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

[r937]

thanks, good to know