-1

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

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 a user views 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 inappropriate I have to loop/remove the id from all the user in the db...

There is any better way?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
k0tt
  • 76
  • 1
  • 7
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Apr 29 '19 at 07:37
  • Time to read a published academic textbook on information modeling & database design. (Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design.) PS There is an enormous difference between relational & non-relational DBMSs. There is no "generic" DB. You see mean, "generic relational DB". Please clarify. – philipxy Apr 29 '19 at 07:39
  • (Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Apr 29 '19 at 07:41

1 Answers1

0

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

camba1
  • 1,795
  • 2
  • 12
  • 18