I have got a listing of synonyms and want to produce a database in SQL for this.

I believed about utilizing a Relational Database Design, try not to determine if it might be the very best. You will see a respectable amount of traffic by using this database.

I believed about Table1 could be like

Id

Table2
Id
InterlinkID (Table1 Id)
Word

Would this be the greatest way? There might be 1 - 20+ linked words. Another problem I see out of this setup is that if I've 1 word that functions as a synonym in excess of a word.

Not too great Illustration of how it will likely be used, but you get the drift:


    Table 1

    Id 1

    Id 2

    Table 2

    Id 1

    InterlinkID 1

    Word One

    Id 2

    InterlinkID 1

    Word 1

    Id 3

    InterlinkID 1

    Word First

    Id 4

    InterlinkID 2

    Word Two

    Id 5

    InterlinkID 2

    Word 2

    Id 6

    InterlinkID 2

    Word Second

Probably the most minimal method of modeling the connection could be like a single table with three posts:

  • id - primary key, integer
  • word - unique word, must have a distinctive constraint to prevent replicates
  • parent_id - nullable

Make use of the parent_id to keep the id quantity of the term you need to relate the present word to. IE:

id  |  word  |  parent_id
---------------------------
1   | abc    |  NULL
2   | def    |  1

...implies that abc was added first, and def is really a synonym for this.

A far more apparent and versatile way of modelling the connection could be with two tables:

  1. WORDS

    • id, primary key
    • wordvalue
  2. SYNONYMS

    • word_id
    • synonym_id

Both posts within the SYNONYMS table will be the primary key, to make sure that there can not be replicates. Nevertheless it will not stop replicates backwards order. However it will help you to map numerous combinations to possess a "spider web" relationship between words, as the single table format would only support a hierarchical relationship.