is one database for images for all users the best idea?

[the_programmer]

hey;
I'm creating a image database. I want each user to register, login and be able to upload images. I however, do not know if each user needs to have their own database for images to be stored in or if I can grab the image via foreign ids from one big database. I don't want users to open their album and see someone elses images... that wouldn't be good! Any help is appreciated!!

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

[the_programmer]

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.

thanks it helps a lot. I am trying to make it so each user will have their own folder on the server... I think I have that covered... I hope. I believe your example is what I'm aiming towards. Thank you. One question.. would I have to put the variable in the path name for the folder that will be made since each person gets their own account?? I would like to just create new folders for each user instead of storing the data in a table that would get bulky quick!!

[vjwilson]

I think you are asking whether you can store the actual images in subfolders for each user, and that should be fine.

The file structure could look something like this:

/images/
|
|-------->oneuser/
| |
| |------>image1.jpg
| |
| |------>image2.jpg
|
|-------->anotheruser/
| |
| |------>image1.jpg
| |
| |------>image2.jpg
|
.... and so on

where oneuser and anotheruser are user IDs in the users table of the database. You could use a mkdir function to create the subfolder when a user creates an account, and then when you want to save or retrieve one of their images you could build the path on the fly with something like:

$path_to_image_file = "/images/" . $username . "/" . $image_to_retrieve;

(You need to make sure there all the characters in the usernames and image names are legal in your webserver's file system. You can limit which characters a user can choose when they sign up. You may want to assign each uploaded image a new name--perhaps based on a timestamp--before you save it permanently.)

I still think you would want to keep a record of each image uploaded in the database, but you would just keep the filename, not the full path, in each record. You could then add meta-data to each image record if you wanted, like a caption, camera used, date taken, tags, etc. (You could store the image data itself in the database, in say a BLOB field, but that would make accessing the images slower.)