Creating composite table to store favorites?
Greetings!
I've queried SQL many times over the last few months, but I've never actually built tables until now.
I'm building an app which, in simple terms, allows users to create user accounts and browse items, and allows manufacturers to post items they offer, and users can "favorite" the items they like.
I have one table with user info:
users
***user_id (PK)
***username
***firstname
***lastname
etc.
I then have a table with the manufacturer info:
manufacturers
***manufacturer_id (PK)
***name
***email
***streetaddress
etc.
Finally, I need a third table that allows each user to favorite as many items as they want. This is where I get lost. I know I need to create a table with a user_id and manufacturer_id, but I also need to be able to add and delete items from the list on a single click. I'm using React/Redux on the front-end, and Node and Express on the back-end. If the user is browsing a list of items and they click the heart, it needs to add it to the favorites table. Conversely, if it's already in the favorites table, it needs to be removed.
I created a table with the following command, but I have no idea if it's what I actually need or not.
CREATE TABLE favorites(
user_id INT NOT NULL,
manufacturer_id INT NOT NULL,
PRIMARY KEY(user_id, manufacturer_id),
FOREIGN KEY(user_id) REFERENCES users(user_id),
FOREIGN KEY(manufacturer_id) REFERENCES manufacturers(manufacturer_id)
);
I guess I have two questions:
1) Is that favorites table sufficient for my purposes?
2) What query do I use that will allow me to add and delete on a single click?
Sorry that was so longwinded. Thanks in advance for any help!