Should I use few large or many small SQL tables?

Question

Hello everyone.
I have a bit of a specific question. I run a website with something close to photo albums. There are a few albums and for each of them I save data into three SQL tables:
1) Pictures (contains addresses to the pictures - each photo belongs only to one album)
2) Objects (contains names of things displayed on the pictures - each picture belongs to one object)
3) Reports (contains reports to the pictures, such as broken link, copyright infringement etc.)

Now my question is, should I continue doing it like this, that means to have separate tables for each album, or is it better to have one table for pictures from all albums, one table for objects from all albums and one table for reports from all albums (I would have to add id of the album the rows belong to into these 3 tables)?

Currently, the tables are named in a way they start with the id of the album, so it looks like this:
4pictures, 4reports, 4objects

So far, I have only 9 albums, but there might be 100 of them in the future and I am not sure if it is practical to have 300 tables in my database.

Answer 1

You can do many things that are correct but I really don't recommend to have a table for each album.

You can create a table for all albums
Like: albums(album_id, album_name);

For pictures you must use album_id as a foreign key. Your table can be like:
pictures(pic_id, pic_name, album_id_ref). "album_id_ref" is your foreign key.

Honestly, I don't understand what are reports and objects in your question.
If you have some reports, e.g. "the link is broken", "uses MIT license", etc, you can create a table like:reports(report_id, report_description) and you can have another table like reportPic(pic_id, report_id)
But if the reports are different in many ways, you can have report table as reports(report_id, report_description, pic_id) and pic_id is your foreign key.
This goes for objects too.
The last thing is if you can have more columns in your tables, do that. For example you want to have a report about pic_link_status and it can be ok or broken, add a col to your pic table with that key as a f_key.

Answer 2

It doesn't make sense to me to have a table for an album. Creating the table, dropping that, checking if it's empty or not, etc.
And what if you want to find a picture by its name or id and you don't know the album?
If your concern is having too many pictures, you can use partitioning. Also take a look at database normalizations.

Answer 3

It depends on which dbms you are using. In mysql or postgresql it's something like:

SELECT * FROM table
WHERE column = "x"
ORDER BY RAND()
LIMIT 1;
In postgresql it is RANDOM instead of RAND.

Answer 4

Yes it is, for large results for your condition. Take a look at:
http://jan.kneschke.de/projects/mysql/order-by-rand/
Hope this helps.

Answer 5

Thanks a lot. I planned to do most of the actions you wrote, if somebody answers me that it is better to have few large tables. By the way, why is it better. Is there better perormance?

Answer 6

Alright, thank you. I was asking, because I often need to choose a random picture that belongs to a specific album and I was worried it might take much longer in a very big table with pictures from all albums.
So one more question: How would you select a random row with a specific value in one of its columns from a very large table?

Answer 7

Thanks. I am using mysqli.
But won't this be unefficient for big tables? If I remember correctly, this will generate a random number for each row in the whole table and THEN it will pick the one with the lowest number.

Answer 8

Great article! It helped me a lot.
Thank you for everything.