I have a design/best practice question.

My app that uses a entity table with a many to many relationship to itself. For example, my entity table is for a 'Word' object. A synonym relationship table relates a word to another word.

I have successfully implemented a many-to-many relationship using a set, but it only works one way. If my relationship table has the columns, word_id and synonym_word_id, and word A is the word_id and instance B is the synonym_word_id, when the two objects are queried from the database, B is listed as a synonym for A, but the reverse does not happen (and I wouldn't expect it to).

What is the best way to handle this pattern?

I was hoping NOT to double enter every relationship (entering A/B and B/A) in the synonym table.

I am not sure it's necessary for synonyms be stored in a set. One alternative would be to just execute a query when synonyms needs to be listed. Currently I don't have a class representing the synonyms table, so I'm not sure how to query against that relationship table (or do inserts/updates/deletes).

Thoughts?

Thanks,
Jay

If you have a many-to-many relation from Word to itself, then Word can have two properties, say forwardSynonyms and reverseSynonyms. For the set of synonyms, combine the two sets from these properties.