Best way to store info in MySQL

[kamyab]

I'm creating a site where members can create their own photo galleries, and I'm going to use mysql to organize the photos.

What's the best way to store the photo gallery information in the database? Should I create one table per user's gallery (many tables, each with a few rows), or should I create a single table where all of the photo records will be stored stored, with a column linking each photo to a user (one table with lots of rows)? What's the best way to do this? Does it even make a difference?

Thank you for your help

[omoutop]

Hi!
u can create one table for photos in order to store the filename and extension while linking every submission to the uploader(user) by using the userID...that is the way I do it for my sites and it works great, no need to have more than one table for this....

[kamyab]

hi,

Thanks for your reply!

I'm planning on having many entries in this database, so I thought that maybe the queries would be quicker depending on which way the database is organized.

[grandpa]

HI kamyab. Welcome to WebmasterWorld!

I would avoid using a single table per user. And a single table might not be the best approach either.
What to do then.. how about two tables. Depending on your needs you might need to expand on this,
but basically you want a one-to-many relationship between the two tables.
Table 1 would have a unique ID for each user. This is would be used to access many rows from table 2,
where you have the photo records stored.

Table 1

UserID - Unique
Name/Description

Table 2

UserID
Image Name/Location

Does that help at all? Here's something from a recent experience.

I built a system for selling a widget. My widget comes in 4 colors, and each color has
different price requirements. Each of those colors comes in hundreds of subtle shades.
I used 7 tables to make it work. The main table is just like the Table 1 above here,
with a unique identifier and description. Each of the 4 colors then gets its own price table.
These are small tables (very few rows each). The next table is my widget status table.
And because I might want to override a price there is a final widget override table.

The next step was to create a class, and write my queries there.
The class will return an array of widgets that I can put on any web page.

So yes, it does make a difference. Sometimes I just have to sit down with a blank sheet
of paper and a pen, and sort out exactly what it is I want to do.

[PeteM]

I did this recently. Deffo 1 table with many rows. To reduce the amount of work that SQL is doing all you need is to create an index of the images you have on your server. I created a 3 column table...

img_user_id
img_seq_no
img_desc

Then I created images using the following naming convention...

userid_'.$img_user_id.'_seqno_'.$img_seq_no.'.jpg

Hope this helps.

Pete

[ergophobe]

Are you aware that there are several excellent image galleries out there ranging from simple ones that are built in to various CMS to full-featured ones that are standalone products like Menalto Gallery, Coppermine... and everything in between?

Honestly, if I wanted a full-fledged multi-user photo gallery, I can't imagine wanting to start one from scratch at this point considering how far along these various projects are and considering the huge tester base.

[kamyab]

Thank you all for your helpful replies.

ergophobe - I am building this for an existing user management system, so all I will really be doing is writing the scripts which will allow users to upload, edit, and delete their photos, as well as displaying the photos in organized galleries. I figured just building this would be easier than going through an existing gallery system and modifying it for my needs. Do you have any suggestions in regards to this?

[ergophobe]

Menalto Gallery is built to be embedded, so I'm not sure. If you want something really simple, then it's probably still faster to just do it from scratch. If you want something "advanced" then it might be worth checking out the menalto embed API.