Database Design For Organizing User Submitted Files

[JasonDFR]

I need some advice / ideas for setting up tables to keep track of user submitted files.

MySQL database.

Each user has a unique user_id. They must be logged in to upload a file.

Users can upload photos, videos, and text files.

The files they upload must have a relation to each other well. For example, a user uploads 4 photos, a video, and a text file based on a common theme. This "group" of files is different than when the same users uploads another set of files based on a different theme.

I am thinking about three different tables, one for each type of file. Four columns in each table. One is a primary key, one the user_id, one the name of the file, and one the path to the directory that holds the file.

Maybe the date of upload as well?

But I can't figure out how to relate them all to each other, especially if the user uploads files from the same "group" at different times.

I am going to use PHP to write the scripts that handle all this.

Thanks a lot! I appreciate the help.

JasonDFR

[james438]

Personally, I like to use as few tables as possible. I would create a table with


create table tablename(
(ID int not null auto_increment,
date datetime,
user_name text,
key_words text,
photos text,
videos text,
primary key(ID))

...figured it would be easier to just show you.

I would store the photos and videos in two folders on your site and store the locations to the photos and videos in your database. The users would be able to upload files on different days/times. The info could be retrieved from the database with something like


select * from tablename where user_name =bob and key_words LIKE '%term%'

of course there are many ways of doing what you want and this is just one method.