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.
3
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.
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.
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.