Speaking from only a small amount of knowledge but having recently implemented a similar system I would say that if the images are being stored as blobs in a table then you want that table to be as lean as possible.

My short answer is that I would take option a

Here's my view: blobs aren't indexible, so the table with the image blobs in it probably wants three fields max, the uniqueid of the image, the userid of the owner and the actual image blob (my table does have 2 additional fields, width & height).

This way if you have a seperate table with all the comments in it, then you could have: commentinguserid, imageid, comment, datetime.

Lastly, you can have your user table with the usernames, the userid's and any other details (email address, password, etc).

Now, when you display an image you only need to do one query to the images table, then using the id of the image you can query the comments table with a join to pull in all the comments along with the usernames of the users who wrote the comments. (You could do this all in one query but then the PHP code would become more complex all for the sake of one additional query).

I would set my indexes up so that:
imagetable: index the imageid and the userid
userstable: index the userid and username
commentstable: index the imageid and the userid

This should result in fairly small indexes and a fast response time.

Now that I've said all that, I'll be very interested to hear other peoples views as I might need to rewrite my tables if someone can show me a better way :o)

Thanks

Seri

Definitely take option a - 3 tables... One for users, one for images, and one for comments. If you're not familiar with database theory, read up on normalization.

You seem to have a situation where 1 user can upload many images, 1 user can comment on many images, and one image can have many comments.

I would go with something like:

tUser: Userid, ... other user attributes ...

tImage: ImageID, UserID (who uploaded it), imageblob (or filename if stored on filesystem instead of in db), ... other image attributes ...

tComments: CommentID, UserID (making comment), ImageID (commented on), comment, ... other comment attributes ...

Typically I prefer that all my tables have a single ID field that uniquely identifies a row. You can still have alternate composite keys. They are both useful for different types of queries.

The idea of having a huge text field where you can store multiple comments delimited by some special character limits your application greatly. Your application will have to do so much extra work to pull comment data out the db, explode the comments field out into it's individual comments, concatenate them back together before inserting the huge string back into the single comment field. You basically will end up writing all of the functionality in your application that the DB gives you out of the box.

Have you thought about what it would take for your application to:

1) update a single comment (like the edit function when you post here in the forums)
2) to delete a comment
3) sort the comments by userid, or insert date, or some other value

If a particular image (ImageID = 87654) has 5000 comments and you only care about displaying the comment(s) from userid = 123 for that image, you will have to pull back all 5000 comments in a huge string just to get at the one comment (or couple comments by the single user) that you are interested in. Why? You could simple SELECT * FROM tComment WHERE ImageID = 87654 AND UserID = 123. Why reinvent the wheel... Trust me... Many people MUCH smarter than you and me have spent 50+ years developing database management systems. Do you really think you can write something better.

What if you'd like to display all comments a particular user has made regardless of the ImageID. In otherwords, show all of a single user's comments for all images they have commented on in your DB. Your problem just got a lot bigger. You might be able to use the DB to pull back the image rows where UserID 123 made a comment but if 1000 images come back, you have to parse all 1000 comment fields to get their data back. If you use a normalized DB you can simply use something like SELECT * FROM tComment WHERE UserID=123 ORDER BY ImageID,CommentID to get to every last one of their comments.

Avoid shortcuts like this. In general it might sound simpler but in the long run, it's going to cost you hours and hours of unnecessary development, debugging, and eventually rewriting your application once you figure out that your application is not nearly as robust as ever DBMS out there today.

[edited by: ZydoSEO at 2:50 am (utc) on June 24, 2008]

Thanks for the answers, I just wanted to keep things as streamlined as possible in the event that the tables start entering the tens of thousands of rows. But I suppose if that is my biggest problem then I should be happy :)