[thelist] Photo Sharing Database

Ann nienell at verizon.net
Wed May 25 08:32:14 CDT 2011

I am creating a photo sharing web app for a group of art students who need to be able to share photos of their work online.  Each student will be allowed 12 albums for the year.  They also want to be able to comment on each others work and reply to these comments.  The initial upload from students need to go into an instructor cue, where the instructor can screen the photos for appropriate content, and then delete or release the pictures to the public.  The site will mainly be viewed by instructors, other students, college professors and potential buyers.  They are not interested in Picasa, but they like the commenting features, and they want to have control over the entire application.  I looked into Coppermine, but not sure if that is a good fit.  I am just looking for guidance from some more experienced coders.

My database has the following tables:
Students (s_id INT AUTO_INCREMENT PRIMARY KEY, f_name VARCHAR(30) NOT NULL, l_name VARCHAR(50) NOT NULL, user_name VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(200) NOT NULL, grade VARCHAR(10) NOT NULL, year YEAR(4) NOT NULL, prime VARCHAR(40) NOT NULL, INDEX (l_name)) ENGINE = InnoDB;
Photos (p_id INT AUTO_INCREMENT PRIMARY KEY, p_name VARCHAR(50) NOT NULL, image MEDIUMBLOB NOT NULL, p_description TEXT, p_type VARCHAR(30) NOT NULL, p_size INT NOT NULL, photo INT NOT NULL, FOREIGN KEY (photo) REFERENCES Albums (a_id), INDEX (p_name)) ENGINE = InnoDB;
PhotoComments (c_id INT AUTO_INCREMENT PRIMARY KEY, photo_id INT NOT NULL, author_id INT NOT NULL, posted DATETIME NOT NULL, body TEXT, FOREIGN KEY (photo_id) REFERENCES Photos (p_id), FOREIGN KEY (author_id) REFERENCES Students (s_id)) ENGINE= InnoDB;
ReplyComments (rc_id INT AUTO_INCREMENT PRIMARY KEY, comment_id INT NOT MULL, author_id INT NOT NULL, posted DATETIME NOT NULL, body TEXT, FOREIGN KEY (comment_id) REFERENCES PhotoComments (c_id), FOREIGN KEY (author_id) REFERENCES Students (s_id)) ENGINE = InnoDB;
LoggedInStudents (s_id INT NOT NULL, session_id VARCHAR(255) NOT NULL, last_access DATETIME, FOREIGN KEY (s_id) REFERENCES Students (s_id), INDEX (session_id)) ENGINE = InnoDB;

I am unsure of how to link the tables to ensure that each student is linked properly to their albums and that the photos are linked properly to the student and the specific album.

 Also, I would like to store the photos in the database, not a filing system.  Will this create problems with archiving?  Instructors want to be able to search the database by student, by year, by photo, etc.

Any insights would be greatly appreciated.

Thank You,

More information about the thelist mailing list