I am working on a new web app that allow users to "save" pictures they like. I have a big table with many pictures and another table with users information. My question: How can I save the pictures each users "save"? I mean what is the proper way to save that information. I thought of making a new table with the user id and the picture id but I think maybe it is too messy and in the future it will take to long to make a query.
Thanks you very much
you can do either things you can either make tables the way you mentioned or you can create different tables for different users {which makes it easier for you to display images saved by each user}
you can use certain nomenclature for tables like <user_id>_savedimages for example you can have a table 21_savedimages for a user with id 21 this makes the task faster and less messy but this in turn results in lots of table in database.
you can decide yourself depending upon no of users you have and average no of images a user is saving.
I'd introduce only a single association table, not one per user.
We introduce a third table, call it "user_picture" or "picture_user", or "saved_picture", (it's just a table name; but it should just make "sense" to someone looking at the model.)
That table will have two foreign keys:
user_id REFERENCES user(id)
picture_id REFERENCES picture(id)
The combination of these two columns can serve as the PRIMARY KEY.
PRIMARY KEY (user_id, picture_id)
To get the saved pictures for a user:
SELECT p.*
FROM picture p
JOIN saved_picture s
ON s.picture_id = p.id
JOIN user u
ON u.id = s.user_id
WHERE u.username = 'foo'
With suitable indexes available, selecting a small subset of rows from large tables should still be very efficient.
This design makes it easy to answer some questions, such as "which pictures are the 'most' saved?"
SELECT s.picture_id
, COUNT(1) AS save_count
FROM saved_picture s
GROUP BY s.picture_id
ORDER BY COUNT(1) DESC
On very large tables, this can crank a while, so this is where having simple, short, surrogate primary keys really helps.
Compare this to the query (or queries) that would be required to answer that same question if you had separate "save" table for each user; consider the number of tables that would need to be queried.
If you start adding attributes to the saved_picture table (e.g. date_saved), you may consider adding a surrogate primary key on the table, and using a UNIQUE constraint on (user_id,picture_id).