1

I'm developing a simple language game about antonyms, synonyms and 'similar speaking' words.

If you play in the 'Synonyms screen', the game will show you two synonyms and two unrelated words and you have to guess wich are the synonyms.
If you play in the 'Antonyms screen', the game will show you two antonyms and two unrelated words and you have to guess wich are the antonyms.
If you play in the 'Speaking screen', the game will show you two 'similar speaking' words, will speak one of each and you have to guess wich is.

My problem is that you can have more than one synonyms, like:

angry: enraged, furious, irated, offended, etc..
love: affection, passion, devotion, etc..

My first idea is something like this one:

Table Synonyms:

+-----------+-------------------+  
|   word    |     related       |  
+-----------+-------------------+  
| angry     |   enraged         |  
| angry     |   furious         |  
| angry     |   irated          |  
| angry     |   offended        |  
| enraged   |   furious         |  
| enraged   |   irated          |  
| enraged   |   offended        |   
| furious   |   irated          |  
| furious   |   offended        |   
| irated    |   offended        |   
| love      |   affection       |  
| love      |   passion         |  
| love      |   devotion        |  
| affection |   pasion          |  
| ....      |   ....            |  
+-----------+-------------------+  

And build the antonyms and speaking tables in the same way.

But it seems like a lot of work and it's difficult to add/remove one synonym.

Also, I don't know where I can put the information about how to speak every word. Maybe another table like:

+-----------+-------------------+  
|   word    |  pronuciation     |  
+-----------+-------------------+  
| angry     |   ˈaNGgrē         |  
| enraged   |   enˈrājd         |  
| ....      |   ....            |  
+-----------+-------------------+  
5

The associative entity idea is a good way to handle this, but it has a drawback that you might want to consider.

Something that hasn't been addressed in some other answers is how you're going to maintain all of this data. It's going to be a lot of work, particularly if you want to be able to get from one word to any of its synonyms (or antonyms) without knowing which of your words you're starting from. Consider your example of {angry, enraged, furious, irritated, offended}. That's a set of five synonyms. If you used associative entities you'd have these pairs: angry=enraged, angry=furious,... etc. You'd also have to do them the other way around: enraged=angry, furious=angry, and so forth.

Five synonyms isn't a lot. However, if you make an associative table for 5 synonyms you're going to have 10 entries. For 6 synonyms you'll have 15 and for 7 synonyms you'll have 21. You can see how this might get out of hand.

Alternative: Association via a root The good news is that there's another way to do this. If you pick one of your synonyms and make it the base or root word (say in your case "angry") then you can have one record for each of these words in one table (five synonyms = five records, include "angry" in your list of words) and then a second table containing just the roots (for example "angry")

The key (pun intended) is to have the list of words point at their base or root word. This would be via a foreign key.

So in your word table, you might have something like this:

  • angry = angry

  • enraged = angry

  • furious = angry

  • irritated = angry

  • offended = angry

Now, you might want to use a meaningless numerical key instead of "angry" in the second column in each of the above. That might make things easier for a variety of practical reasons. In that case you'd have a second table with just the base words with their meaningless numerical keys, for example:

  • 123, angry

So your word table looks more like this:

  • angry, 123

  • enraged, 123

  • furious, 123

  • irritated, 123

  • offended, 123

So how do you find synonyms for a word?

That's a simple process, but it's a two step* process:

  1. Look up the root key for the given word. Say you want to find synonyms for "furious". You look it up and find the root is number 123.

  2. Look for all the words that have the same root key as your word, these are your synonyms.

The advantage of doing it this way is that it saves you a ton of data maintenance. You only have to maintain one record for each word, plus one for each group of synonyms. That's a lot fewer than combinations of all synonyms, which is what you're looking at if you do this with an associative entity.

*Note: you don't actually have to do this in two steps. You can write a SQL which combines all of this into a single statement using a self join.

| improve this answer | |
  • Thank you, your solution was amazing!! I like a lot your proposal because I will have more tables with the same paradigma and this one saves me from a lot of data maintenance. – Juan Antonio Tubío Dec 1 '19 at 17:56
  • It's an interesting idea but what about the fact that synonyms are not exact matches? E.g. enraged = angry = annoyed = displeased = vexed = disturb = amaze = perplex... (chain followed on thesaurus.com). Now, I don't know about you, but to me enraged is not perplex(ed). I can think of many such chains where a slight difference in meaning can lead to oxymoronic faux-synonyms. Better IMHO to give each word its own set, rather that to attempt grouping. +1 for a different approach :-). Not all words y1... yn which are synonyms of word x are synonyms of each other! – Vérace Dec 1 '19 at 19:02
  • Say, there are 40,000 (on the high side IMHO) words in the average vocab and 5 synonyms per word that makes (40k x 5) x 2 = 400k entries. Nothing for the average db with an index! – Vérace Dec 1 '19 at 19:08
  • @Vérace I agree with the point that synonyms have nuanced meanings and so they aren't equal to each other. No two direct synonyms are exactly the same semantically so you certainly couldn't follow a chain of several synonyms and expect the meanings to be identical from one end of the chain to the other. I also agree that 400,000 records is a piece of cake for a database system. These are both good points. I think my main point was, however, that while it's nothing for a machine, it's a lot of data for a human to curate. – Joel Brown Dec 2 '19 at 4:20
2

This is a classic case of where one would choose to use a linking table or Associative Entity in more technical language (also called bridging, joining or mapping tables inter alia). It is a bit unusual in that it requires a self-referencing linking table (which leads to complications re. constraint deferral - see below).

What I would do is something like this (see the fiddle here):

CREATE TABLE word
(
  word_id INTEGER NOT NULL AUTO_INCREMENT,
  the_word   VARCHAR (50) NOT NULL, -- I don't like having tables and columns with the same name
  pronunciation VARCHAR (50), -- allow NULLs for now - you can change it later
  CONSTRAINT word_pk PRIMARY KEY (word_id),
  CONSTRAINT word_the_word_uq UNIQUE (the_word)  
    -- no duplicated words!
    -- although this is interesting, what about, say, bear, bear and bear, one is
    -- a large mammal, one is to carry and yet another is to tolerate something.
    -- I leave this up to you! Maybe another field for meaning? UNIQUE (the_word, meaning)?
);

Notice that already, with no programming done, you can constrain your data in all sorts of interesting ways - reducing the possibility of inconsistencies or errors creeping in.

Populate the table:

INSERT INTO word (the_word, pronunciation)
VALUES
('angry', 'ˈaNGgrē'), ('enraged', 'enˈrājd'), ('furious', NULL), ('irated', NULL), 
('love', NULL), ('affection', NULL), ('passion', NULL), ('devotion', NULL);

Then:

CREATE TABLE synonym
(
  word_1 INTEGER NOT NULL,
  word_2 INTEGER NOT NULL,
  CONSTRAINT synonyms_not_match_ck CHECK (word_1 != word_2),
  CONSTRAINT synonyn_pk PRIMARY KEY (word_1, word_2),
  CONSTRAINT synonym_w1_fk FOREIGN KEY (word_1) REFERENCES word (word_id),
  CONSTRAINT synonym_w2_fk FOREIGN KEY (word_2) REFERENCES word (word_id)
);  

Again, notice how much you can do with no coding.

Populate:

INSERT INTO synonym VALUES 
(1, 2), (1, 3), (1, 4), 
(2, 1), (2, 3), (2, 4),
(3, 1), (3, 2), (3, 4),
(4, 1), (4, 2), (4, 3);

In the fiddle, I've performed the same procedure for antonyms, but I won't put it here - too long!

To SELECT all the synonyms of 'enraged':

SELECT w.word_id, w.the_word, w.pronunciation
FROM word w 
WHERE w.word_id IN   
(
  SELECT word_2 FROM synonym 
  WHERE word_1 =
  (
    SELECT word_id FROM word WHERE the_word = 'enraged'
  )
);

Result:

word_id the_word    pronunciation
      1    angry          ˈaNGgrē
      3  furious    
      4   irated    

DELETE the synonyms of 'enraged'

DELETE FROM synonym 
WHERE word_1 = (SELECT word_id FROM word WHERE the_word = 'enraged')
OR    word_2 = (SELECT word_id FROM word WHERE the_word = 'enraged');

Check:

SELECT * FROM synonym; -- word_2 = 2 no longer exists, but it still exists in the word table

Result:

word_1  word_2
     3       1
     4       1
     1       3
     4       3
     1       4
     3       4

Then delete all reference to 'enraged' in the word table.

-- this now works because no FKs now point to it
DELETE FROM word where the_word = 'enraged';  

And, from the fiddle, it's gone!

You might be better off using PostgreSQL which supports deferred constraints. They don't work on the fiddle for PostgreSQL, but you can experiment in your own environment if you are interested.

In future, please always include your MySQL version - it's important for every server, but particularly so for MySQL now (end 2019) because they have relatively recently introduced sophisticated features such Window functions and Common Table Expressions which not everybody is running, but can be crucial to solve certain problems!

p.s. welcome to the forum!

EDIT!

p.p.s Just for kicks, I tried to get PostgreSQL to do deletes in a single statement using cascading deletes with deferred constraints. I haven't been able to get it running yet, but in the meantime there's a way of doing it in PostgreSQL that is quite neat.

From here (the accepted answer), in PostgreSQL, a single statement can be made up of several Common Table Expressions - AKA the WITH clause. So, we can do this (see fiddle here):

-- Works as a single statement
-- FROM here: https://stackoverflow.com/questions/50604838/deleting-with-self-referential-foreign-key-in-postgres  
WITH cte1 AS
(
  DELETE FROM synonym 
  WHERE word_1 = (SELECT word_id FROM word WHERE the_word = 'enraged')
  OR    word_2 = (SELECT word_id FROM word WHERE the_word = 'enraged')
),
cte2 AS
(
  DELETE FROM word WHERE the_word = 'enraged'
)
SELECT COALESCE(NULL, 'CTE succeeded') AS end_of_query;

Result:

end_of_query
CTE succeeded

Belt and braces:

-- Empty set. PostgreSQL really is the dog's!
-- 0 is impossible for word_1
SELECT word_1 AS x, CAST(word_2 AS TEXT) AS y FROM synonym WHERE word_1 = 2 OR word_2 = 2
UNION
SELECT word_id, the_word FROM word WHERE the_word = 'enraged';

Result:

x   y   <<==== Headers
        <<==== Nothing, i.e. empty set, i.e. desired result!

I still think that a single DELETE from word of the 'enraged' should delete all references to it using CASCADE - will try and figure it out tomorrow! MySQL doesn't have DEFERRED constraints and its CTEs won't let you do this either!

| improve this answer | |
  • 1
    Is SQL a synonym for code? :-) Yes, of course, you're correct. I was just trying to stress that one can put a lot of control over the data before starting to programme proper (i.e. using C, Java... whatever). IMHO, it is vastly preferable to put constraints in the db rather than coding for them. And of course, SQL is Turing complete now! – Vérace Nov 25 '19 at 18:19
  • Thank you so much for your effort and your really completed answer. I will take for another similar situation. – Juan Antonio Tubío Dec 1 '19 at 17:57
  • Glad I was able to help - it was a learning experience for me also. I'm planning on revising it though - I want to get to grips with DEFERRED constraints - this particular question is on my todo list - hope to get to it during the week. – Vérace Dec 1 '19 at 18:46
  • Check out my comment on your accepted answer - I don't think it's a runner for the reasons outlined! – Vérace Dec 1 '19 at 19:07
0

Schematically:

CREATE TABLE words ( id SERIAL PK,
                     word VARCHAR UNIQUE );

CREATE TABLE relation_type ( id SERIAL PK,
                             relation VARCHAR UNIQUE );

CREATE TABLE relations ( id SERIAL PK,
                         word_id BIGINT FK words (id),
                         related_id BIGINT FK words (id),
                         relation_type BIGINT FK relation_type (id) );
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.