Multiple many to many relationships between same tables or one n:m relationship with identifying attribute?

Currently I have multiple n:m relationships between 2 tables:

Users --> Favourites(user_id,post_id) <-- Posts
Users --> Follow(user_id,post_id)     <-- Posts

Would you rather have 2 join tables or just one join table with an attribute which marks the type of the join, so something like:

Us开发者_如何学Goers --> Users_Posts (user_id,post_id,type(VALUES="favourite,follow") <-- Posts

It's not exactly the same example as I have in my application, but I think you can get the idea.

I don't think there is one "right" answer here. I think it depends. If you use a single table with a "relationship type" column and frequently want to extract just relationships of a single type--say just favorites--then each query against that table will need to apply a WHERE clause to filter out the types you don't want. That might make for slow queries if you don't properly index the non-key "relationship type" column. Also, it makes it so future developers will always need to know and remember to filter to the types they want or they may unexpectedly get relationships data back that they don't intend. Having two separate tables is easier to understand. For example, it is easier for me to quickly know what to expect in a "Favorites" table than in a "Users_Posts" table, so separate tables may communicate the differences more quickly.

On the other hand, if you frequently need to select both relationship types in a single set, then having them in a single table is simpler because you don't need to worry about doing a UNION to combine the data from two tables into a single view. What if there were 10,000 different possible relationship types? Would you want 10,000 different tables, or would you prefer a single table? Most people would prefer a single table in that case.

So I think it depends on many factors, such as expected usage, size, etc. The "right" answer is more of an art than a science.