Search code examples
sqldatabase-designinner-join

SQL linked words - two way relationship

Hello fellow SQLarians!

I am just trying to build a tool for managing vocabularies. In the process of thinking through the database design, I got stuck with figuring out how exactly I could make a relationship so, that when linked both items always know from each other. Here is my main Table VOCABULARY:

- [generated_id] INTEGER PRIMARY KEY
- [word] TEXT
- [translation] TEXT
- [pos] TEXT
- [example_sentence] TEXT
- [example_translation] TEXT
- [description] TEXT
- [related_words] ???
- [related_image] BLOB

So I want to give every word some related words. But when I do, I also want to automatically give this word as related word to the linked words, so I don't have to link it two times.

My first approach was an extra Table with related words, like this:

- [word] INTEGER
- [rel_word_1] INTEGER
- [rel_word_2] INTEGER
- [rel_word_3] INTEGER
- [rel_word_4] INTEGER
- [rel_word_5] INTEGER
...

But this would work two way only when I have lots of duplicate data in it, eg.:

Word     | rel_word_1

word     | rel_word
rel_word | word

How is something like this achiefed, are there best practices?

Cheers!

Solution

  • You can create extra Table with two column

    WORD_TABLE

    • [generated_id] INTEGER PRIMARY KEY
    • [word] TEXT
    • [translation] TEXT
    • [pos] TEXT
    • [example_sentence] TEXT
    • [example_translation] TEXT
    • [description] TEXT
    • [related_word_group_id] INTEGER FK_RELWORDGROUP_WORD
    • [related_image] BLOB

    RELWORDGROUP_TABLE

    • [related_word_group_id] INTEGER PRIMARY KEY
    • [rel_words_ids] TEXT ( eg. "word_id1:word_id3:word_id5:... etc")