Storing a boolean bit for a many-to-many relation

I'm writing a forum system (in Ruby, using Sequel), and one of the requirements is for users to be able to "star" a thread, which is vaguely equivalent to "subscription" features most forums support. I'm unsure about how to store the starring in the database, and especially on how to query for starred/unstarred threads for a given user, or checking whether a thread is starred.

Any tips would be greatly appreciated, and if you happen to know your way around Sequel, an example model would be absolutely grand.

Solution

This is very simple to implement:

First your migration:

create_table(:subscriptions, ignore_index_errors: true) do
  primary_key :id
  column :created_at, 'timestamp with time zone'
  foreign_key :user_id, :users, null: false, key: [:id], index: true, on_delete: :cascade
  foreign_key :thread_id, :threads, null: false, key: [:id], index: true, on_delete: :cascade
end

Your Models:

app/models/subscription.rb

class Subscription < Sequel::Model
  many_to_one :user
  many_to_one :thread
end

app/models/user.rb

class User < Sequel::Model
  one_to_many :subscriptions

  many_to_many :subscribed_threads,
    class:      :Thread,
    join_table: :subscriptions,
    left_key:   :user_id,
    right_key:  :thread_id
end

app/models/thread.rb

class Thread < Sequel::Model
  one_to_many :subscriptions

  many_to_many :subscribers,
    class:      :User,
    join_table: :subscriptions,
    left_key:   :thread_id,
    right_key:  :user_id
end

Query as follows

# all threads a user is subscribed to
user.subscribed_threads

# all subscribers to a thread
thread.subscribers

# all subscriptions to a thread in the last 3 days
thread.subscriptions.where{created_at >= Date.today - 3}

I suggest also configuring the dataset associations plugin on all your models:

# Make all model subclasses create association methods for datasets
Sequel::Model.plugin :dataset_associations

You can then compose and chain queries through associations with conditions more conveniently:

 # all new subscribers for a thread in the last 3 days who are moderators
thread.subscriptions.where{created_at >= Date.today - 3}.user.where(moderator: true)

There are some powerful filtering and querying possibilities: