Database Structure For "Bookmarking" Photos

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
BZorch
Forum Commoner
Posts: 45
Joined: Mon May 02, 2005 10:42 pm

Database Structure For "Bookmarking" Photos

Post by BZorch »

I have a photo database and I want my users to be able to save their favorite photos in custom folders. At the moment, I am not limiting how many folders they would be able to create nor the number of photos they can store in each folder.

My questions is how would you recommend that I set up the database? I have created a user database that stores their id, folder name, and photo ids. I have come up with two ways to organize it.

1. Store each individual photo id as an individual record. Example (1) This would end up creating a very large database overtime though it would be very easy to query to display photos becaue the photo id field would match exactly with the photo database.

2. Store an array of photo ids for each folder. Example woudl be (1, 2, 3, 4, 5, 6, 7). This would reduce the number of records, but I do not know of an easy way to query the photo database to retrieve their favorites because the exact information does not match the field. So I would have to figure a way to pull out the array and then query for each individual record. Is there a way to do this easily?

Would you recommend either of these approaches? If not, how would you set it up?

Any insight would be appreciated.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Database Structure For "Bookmarking" Photos

Post by timvw »

BZorch wrote: 1. Store each individual photo id as an individual record. Example (1) This would end up creating a very large database overtime though it would be very easy to query to display photos becaue the photo id field would match exactly with the photo database.
How do you define 'very large database'? And how long would it take before your database becomes one? In my experience it has never been a good argument to not choose this option.
BZorch wrote: 2. Store an array of photo ids for each folder. Example woudl be (1, 2, 3, 4, 5, 6, 7). This would reduce the number of records, but I do not know of an easy way to query the photo database to retrieve their favorites because the exact information does not match the field. So I would have to figure a way to pull out the array and then query for each individual record. Is there a way to do this easily?
Check out your dbms manual.. Eg: MySQL has field operators..

Imho currently your logical design is quite bound to the physical implementation. How would you implement 'logical folders' that share a file? In your current design you would have to copy them...

In the situation of a 1 - n (each folder can have many file, each file belongs to exactly one folder) the standard solution is to add a column to the table for files which is a foreignkey to the file table...

In the situation of a n - m (each folder can have many files, each file can belong to many folders) the standard solution is to add an extra 'link' table that has pairs of (folder_id, file_id).
BZorch
Forum Commoner
Posts: 45
Joined: Mon May 02, 2005 10:42 pm

Post by BZorch »

Thank you for responding. I can envision the database getting up to 500,000+ records within a year. Which seems like a lot, but I do not have any frame of reference to what is a lot to MYSQL. Would the speed be dependent on my server configuration? If so, which aspect is the most critical (i.e. procecessor, RAM)

If this size of database is not really considered cumbersome then storing them individually may be an option. I may actually be providing the "link" database as you suggest. My photo information is stored in its own database. The users favorite is stored in a separate database that just records the name of favorites folder, user id, and which photos id is associated with the folder. I then would pull the photo locations from the other database using the user favorites database. So the 500,000 records is not storing very much data per record. As along as I am not using a boolean method for pulling data it may be fast. I would only search the database to match the specific criteria of the user and the folder.

Logically the individual record for each photo stored as the favorites is easy to understand and implement. Basically I can pull all photo locations from the photo database matching the users id and database name selected in the user database. I was just worried about the size of the database. But it may not be a problem.

By updating a favorites folder, to include more than one photo id in the record. I was finding it hard to quickly pull the information and search for the photo is the other database. It seemed too cumbersome and complicated. At least I thought I had to pull the record Example 1,2,3,4 and then break it up into individual numbers and then search for each individually. This seemed ridiculous.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

The first example you provided is the most logical. Database sizes can get enormous, so enormous that the only limit may be the file size limit on your operating system.

Using correct indexes on your database tables, and usage of limit clauses, your queries could still be extremely fast with extremely large tables.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
BZorch
Forum Commoner
Posts: 45
Joined: Mon May 02, 2005 10:42 pm

Post by BZorch »

Thanks, if I understand correctly the size does not greatly effect the speed as long as it is indexed and is looking up a specific record. I was hoping this would be the case. I just wanted to confirm it before I continued forward.

BTW, how large can a file get on a Linux server?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

BZorch wrote:Thanks, if I understand correctly the size does not greatly effect the speed as long as it is indexed and is looking up a specific record. I was hoping this would be the case. I just wanted to confirm it before I continued forward.
Only one way to know for sure... Fill your database with lots of data... And see if it still works...
BZorch wrote: BTW, how large can a file get on a Linux server?
It depends on the filesystem you're using.
BZorch
Forum Commoner
Posts: 45
Joined: Mon May 02, 2005 10:42 pm

Post by BZorch »

Thank you for the insight. I intend to keep it simple. I just needed to get a sense from more experienced people like yourself if I was going in the right direction.

Testing 500,000 records would be tough though.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Here's my tuppence worth:

I'd have a table for Users (T_USER), a table for folders (T_FOLDER), a table for Pictures (T_PICTURE), a table linking Users to Folders (T_USER_FOLDER), and a table linking folders to Pictures (T_FOLDER_PICTURE).

When you create a new user, (s)he is put into the Users table, with Primary Key "ID_USER".

When (s)he creates a folder, the folder is saved into T_FOLDERS, with primary key ID_FOLDER, and it's description (DE_FOLDER). A new row is also created in the T_USER_FOLDERS table, with primary key (ID_USER, ID_FOLDER).

When the user adds images to the database, they are saved in the T_PICTURE table, with primary key ID_IMAGE, and other data Eg: image name, image size etc.

When the user adds the images to a folder, a new row is created in the T_FOLDER_PICTURE table, with primary key (ID_FOLDER, ID_PICTURE).

To see all the folders for each user, use

Code: Select all

SELECT F.ID_FOLDER, F.DE_FOLDER 
FROM T_USER_FOLDER U, 
     T_FOLDER F
WHERE U.ID_USER = '$user'
AND U.ID_FOLDER = F.ID_FOLDER
To see all images for each user, use

Code: Select all

SELECT P.ID_PICTURE, P.DE_PICTURE 
FROM T_USER_FOLDER U,
     T_FOLDER_PICTURE F, 
     T_PICTURE P 
WHERE U.ID_USER = '$user'
AND F.ID_FOLDER = U.ID_FOLDER
AND P.ID_PICTURE = F.ID_PICTURE
A database set up like this allows for easy maintenance - if a user wants to move a lot of photos to a different folder, you have to update only the T_FOLDER_PICTURE table, if a user wants to delete a folder, you can delete the record in the T_USER_FOLDER table, and (if set up correctly) cascade this delete to the T_FOLDER_PICTURE table too.

You'd probably need to index the T_FOLDER_PICTURE table by ID_PICTURE, but all the other tables should be ok with just their primary keys.

As I said at the beginning, my tuppence worth. I'd be interested to hear other opinions too.
Post Reply