Posts by vjwilson

I think you can easily use just one table to store the images, or better yet, filepaths to images that you save in an directory on your filesystem.

Then, just have a foreign key in your images table that references your user accounts table. When a user wants to see only her images, your app can retrieve just those images with a "WHERE account_id = $account_id" (in PHP for instance).

You could also add a flag to each image record which indicates whether the image is also available for public view, a la Flickr.

For example,

CREATE  TABLE IF NOT EXISTS `accounts` (
  `account_id` INT(10) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(255) NOT NULL ,
  `password` CHAR(64) NULL ,
  PRIMARY KEY (`account_id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `images` (
  `image_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `filename` VARCHAR(255) NOT NULL ,
  `account_id` INT(10) UNSIGNED NOT NULL ,
  `is_public` TINYINT(1) NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`image_id`) ,
 INDEX `fk_image_account1` (`account_id` ASC) ,
  CONSTRAINT `fk_image_account1`
    FOREIGN KEY (`account_id` )
    REFERENCES `accounts` (`account_id` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I hope this helps.