mysql php commenting system: new database or looping through ids?

Question

I want to make a picture website with a mysql php commenting system for each picture. Now, I can do two things: 1) use a new database for each picture or 2) use a single database + an id for each picture. The problem with the latter is that I need to loop the whole database everytime I want to find the comments for a picture. What's the most obvious way to handle this?

Answer 1

With very few exceptions you will put all of the things in a single database. The only reason for using multiple databases is extreme scaling.

You won't have to "loop" through the database if your tables are properly indexed. You can fetch records like this:

SELECT * FROM images WHERE id=192

This will retrieve one record if present.

If you're a little wobbly on SQL, then you'll either need to read up on that, or use a document store that doesn't require it.

Answer 2

Use one database with multiple tables for everything that fits logically together and use queries to get the needed data from the database, if you create good queries you do not need to loop through the whole database every time. Using a separate database for every item is missing the point of a database all together. Get some information of what a database is and how they function.

Answer 3

I have no idea as to why you think you need a new database. You probably only need two tables, one for the pictures, and one for the picture comments, which has a foreign key to the picture table.

When you need top look up the comments based on the picture id you would just do something like

SELECT * FROM picture_comments WHERE picture_id = ?