SQL database structure

Question

I have a list of synonyms and you need to create a database in SQL for it.

I was thinking about using a relational database design, but I don't know if it would be better. With this database there will be a decent amount of traffic.

I thought Table1 would look like

Id Table2 Id InterlinkID (Table1 Id) Word 

Would this be the best way? There may be 1-20 + related words. Another problem that I see from this setup is if I have one word that works as a synonym for more than one word.

Not so great An example of how it will be used, but you get the idea:

     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

Answer 1

The smallest way to model relationships is like a single table with three columns:

• id - primary key, integer
• word - a unique word, must have a unique restriction to stop duplicates
• parent_id - nullable

Use parent_id to store the ID number of the word to which you want to bind the current word. IE:

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

... shows that abc was first added, and def is a synonym.

More obvious and flexible relationship modeling tools will be associated with two tables:

• WORDS

  • id , primary key
  • wordvalue

• SYNONYMS

  • word_id
  • synonym_id

Both columns in the SYNONYMS table will be the primary key so that there are no duplicates. However, it will not stop duplicates in the reverse order. But this will allow you to display multiple combinations in order to have a web connection between words, while a single table format will only support hierarchical relationships.