You should never use multiple columns to hold more than one of the same item. This is bad database design and will be a major pain to use (try to write a query that will fetch and display all of a user's photos, or delete one photo, and you will see what I mean).
Another issue is that you might want to store other info about each photo--such as when it was uploaded and what format it is. Instead of 20 columns, suddenly you might have 60 or 80 columns in your table. Ugh!
Instead, have a table of photos that is separate from the table of users. This is linked to the user table on user ID. You can enforce the 20 photo limit with a table constraint, or simply by checking the number of photos programmatically before you allow upload.
Primary Key | | |
USER_ID | FIRST | LAST | ETC.
12345 | John | Smith | ... Any other user-specific columns
Primary Key | Foreign Key | | | |
PHOTO_ID | USER_ID | PHOTO | FORMAT | DATE | ETC.
13516 | 12345 | <data> | JPG | 2013-05-01 | ... Other photo-specific columns
If you set the foreign key for photo to
on delete cascade, a user's photos will automatically be deleted if you delete the user.
See this StackOverflow question for info on a table constraint to limit the number of photos per user.
Getting the photos for a user uses a simple query:
select * from photo
where user_id = 12345;
If you need photos and user information like the name, use a join:
select first, last, photo from photo p
inner join user u on p.user_id = u.user_id;
Update: I do agree with Pieter B that, in most cases, you are probably better off storing the images separately in the file structure, and just making the
images table store the file name of each image. This will make it a lot easier to, say, display the images on a web page. But you will also need extra code to make sure the directory stays in sync with the table--if you delete a user, you have to go manually delete all of the images.