How to properly design a simple favorites and blocked table?

Question

Hey,

i am currently writing a webapp in rails where users can mark items as favorites and also block them. I came up two ways and wondered which one is more common/better way.

1. Separate join tables

Would it be wise to have 2 tables for this? Like:

users_favorites
  - user_id
  - item_id

users_blocked
  - user_id
  - item_id

2. single table

users_marks (or so)
  - users_id
  - item_id
  - type (["fav", "blk"])

Both ways seem to have advantages. Which one would you use and why?

Answer 1

I would go with #2.

It leaves all the appropriate data in a single table.

Otherwise you might have to resort to a union or distinct joins to get a full list of details.

Answer 2

The second one has at least the advantage (if the primary key is users_id + item_id) to make sure that no user will have an item both as favorited and blocked.

I suppose I would got with that second solution -- especially considering the two tables, in the first solution, would have the same structure, which seems strange ; and it also allows you to have all the information in the same place, which might help, in some cases (reporting, for instance ? ).

Answer 3

It's just a different status of an item, so #2 will do the job. What would you do if it would be colors? Two different tables? I don't think so ;)

Edit: You might want the status in a different table and link it with a foreign key, but that's up to you. It depend on how many different status you expect to have. Just these two or many others as well?