Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am developing an app which allows a user to upload a maximum of 20 photos, the straight forward way is of course adding 20 columns to my database table but if the user only uploads 1 image, the remaining columns becomes redundant. Anyone can advice me on a more efficient way to design it?

Thanks.

share|improve this question
    
Create a separate table for photos, set Users PK as a foreign key, add a column with the number of the uploaded photos to the Users. –  superM Apr 22 '13 at 8:27
    
SELECT COUNT(*) FROM photos WHERE user_id = :id before inserting new photos? –  deceze Apr 22 '13 at 8:31
2  
--the straight forward way is of course adding 20 columns to my database table-- That's the worst way you can do it. –  Pieter B Apr 22 '13 at 9:46

3 Answers 3

up vote 9 down vote accepted

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.

Table user:

Primary Key |       |       |
USER_ID     | FIRST | LAST  | ETC.
12345       | John  | Smith | ... Any other user-specific columns

Table photo:

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.

share|improve this answer

In general I would not store photos in binary form in a database. What I would do is store photos on disk and store the path to that photo in the database.

Other answers already showed how to create a seperate table, use that but with the references instead of the binaries.

share|improve this answer

You add a child table

PhotoID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
UserID int NOT NULL -- FK to User table,
Photo varbinary(max)

This changes columns into rows, quite simply as per first normal form

There are storage benefits because you now no longer have potentially 20 BLOBS in one user row. Your user table has a lot less memory footprint for other columns.

And you can allow more then 20 photos very easily if you decide to allow more.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.