I am working on a database which as a user table and a party table. A user can go to many parties and the party can host many users. So this is a many-to-many relationship. But since its not possible to have a many to many relationship between two tables, I created a joiner table called 'user_party' table. Now I am not sure what goes in the party table and what goes in the user_party table. For example, user table has basic columns like user_id, user_name, user_email, etc. and party table has information like party_description, party_time, party_location, etc. Should these party table values go to user_party table instead? What values should the joiner table include and what goes in the party table?

CodePudding user response：

I think I have a solution for you. As I understand from your description you need to change your schema a little bit, and your table should be look like below.
User(user_id, user_name, user_email);
party (party_id,party_description, party_time, party_location);
user_party (id,party_id,user_id);
hostuser_party (id,party_id,user_id);

Note: hostuser_party table is required if a party hosted by multiple users. If party always hosted by one user then you can add host_id(User_id) column in party table.

CodePudding user response：

The joiner table should be here to maintain the relation between a particular user and a specific party, and in your case, not more.

Before all, the party should be uniquely defined with a party_id primary column. Then, a user_party table could be define with only two columns: a user_id and a party_id.

So, the join would involve 3 table rows uniquely identified:

• One from the user table (identified by the user_id)
• One from the party table (identified by the party_id)
• One from the user_party(identified by the t-uple [ user_id, party_id])

The user_party table could only embed some data which are meaningful for single occurrences of the [ user_id, party_id] t-uple itself, such as the number of beer cans bringed to a particular party :)

Eventually, these data could be externally stored into a specific table in order to keep only the relational aspect there and the t-uple extended with another key.