Best way to interconnect data to lower db queries [generic, no language specific]

Question

I'm making a bot that receive and send images, i have to keep track to wich image is sent to who so it send it only once. An user can also flag the image as inappropiate.

I made a db with 2 tables:

• userTable with userID and userName
• imageTable with imgID, fileName, fileCRC

I can think only of:

a) add viewedBy to imageTable "user1,user213,user9"
or
b) add imageToView to userTable "123,545,21321,654565"

But if i do [a] there is the problem that the more images an user view the more time is needed to get one random image.

And if i do [b] i already have a list of unseen images so i can just pick one random from here then delete the id. But if one user flag it as inappropiate i have to loop/remove the id from all the user in the db...

There is any better way ?

Answer 1

You need an intermediate table that keeps track which user has seen which image. So basically this new table, let’s call it imageByUser, would contain a user id, an image id, inappropriate boolean flag, dateseen datetime (optional but would be useful on the long run) and a generated primary key (or you can have the combination of the user, the image and the dateseen as a composite logical primary key instead).

Having this third table would solve all your problems as you would just add a new row when someone sees an image. Also if they mark it as inappropriate m all you have to do is update the inappropriate flag to yes. This way you could even keep track of the cases when a user happens to see the same image twice (just add another row to the table).