I am very open to edits of the title
Basically I am making a site about petitions. I want a user to be able to sign more than one petitions and be able to see list of petitions he signed. I am wondering what is the right approach to do it.
Approach 1 that I thought of: Every petition has a unique id and every user has a unique id. To my petitions table I could add a varchar column named “signedUsers” and there I could add users id with a ‘,’, then pull it out as a string, separate it by ‘,’ and display every petition a user has signed.
Approach 2 that I thought of: Similar to the first one, with the difference that instead storing signed users to the petitions table, I could store “signedPetitions” to the users table.
Those are the approaches that I can think of, but it’s my first time doing it and I don’t know if there is “known” or “common” approach to such situations, so I am open to some enlightenment.
P.S. Not sure if this is the proper place to ask question without code, so I am sorry if it is not.
Advertisement
Answer
You want a table userPetitions
with one row per user and one row per petition. This table would have a user id and petition id, with foreign key relationships back to those tables.
This is called a junction table or association table and is the right way to store many-to-many relationships in SQL.