database schema for like entities that can be combined pairwise (ActiveRecord) -

i designing database of woodwind instrument sounds, , create table joins pairs of sounds performer can combine into, example, trill. such relations transitive: if sound has 'sound relation' sound b, sound b has same 'sound relation' sound a.

i familiar join tables, i've never seen them used join 'like' objects, join 'unlike' objects, such tags , posts, i'm wary of going direction.

i realize example below looks extremely dubious, gives idea of i'm after. better way of doing it? (using activerecord syntax)

models

class sound < activerecord::base   has_many :linked_sounds, through: :sound_relations, class_name: "sound", foreign_key: ??? end  class sound_relation < activerecord::base   has_many :sounds end 

migration

class createsoundrelations < activerecord::migration   def change     create_table :sound_relations |t|       t.integer first_sound_id  # cannot possibly right.       t.integer second_sound_id # surely transitivity of                                  # relationship should more evident?     end   end end 

you might try like:

class set  < activerecord::base   has_many :sound_sets   has_many :sounds, :through => :sound_sets end  class soundset < activerecord::base   belongs_to :sound   belongs_to :set end  class sound < activerecord::base   has_many :sound_sets   has_many :sets          , :through => :sound_sets   has_many :set_sound_sets, :through => :sets          , :source => :sound_sets   has_many :set_sounds    , :through => :set_sound_sets, :source => :sound end 

so, no more "sound_1" , "sound_2" ... both sounds. every sound can use set_sounds method retrieve of sounds associated it.

this allow more 2 sounds in relation, , might put "type" on sets model.

edit: if @ query generated, you'll find sound_sets mentioned in there twice, once different alias. key eliminating "self" joins include clause in association along lines of:

has_many :set_sounds    ,          {where("sound_sets.sound_id != set_sound_sets_sound_set_sounds.sound_id")},          :through => :set_sound_sets,          :source => :sound     

... "sound_set_sounds" table alias. if can post query in comments can update actual alias.