multiple pointers in SQLite

Question

I am trying to re-write a dictionary program in python(3.X). I have been using JSON but thought it could be fun to try my hand at SQLlite. My question is:

I have:

1. a word
2. a list of sentences using the word
3. a list of synonyms
4. a list of antonyms

what is the the cleanest way to organise the data using SQLite3?

what I thought was to one table with unique id's for the 'val and example' and then create new entries for synonyms and antonyms if they do not yet exist.

ValId Val Eaxmples
1 'hot' 'the water is hot\n she's hot\n'
2 'warm' '...'
3 'cold' '...'
4 'freezing' '...'

but then how would I create a table that could point back to multiple id's? for example 'hot' points to 2 as a synonym and 3 , 4 as antonyms. How would I create multiple ponters, do I have to put them in a sting and then parse them, as I do not know how may synonyms or antonyms might be created.

LinksID ValID SynIDs AntsIds
 1 1 2 3,4

Answer 1

I would create two separate tables, one for synonyms and one for antonyms where you have FOREIGN KEYS to reference your words:

CREATE TABLE synonym (
 SynID INTEGER,
 WordID1 INTEGER,
 WordID2 INTEGER,
 FOREIGN KEY (WordID1) REFERENCES word(ValID),
 FOREIGN KEY (WordID2) REFERENCES word(ValID)
);
CREATE TABLE antonym (
 AntID INTEGER,
 WordID1 INTEGER,
 WordID2 INTEGER,
 FOREIGN KEY (WordID1) REFERENCES word(ValID),
 FOREIGN KEY (WordID2) REFERENCES word(ValID) 
);

Then you don't have to worry about having an unknown number of synonyms and antonyms for each word. You would then get the following structure:

Your word table:

ValId Val Eaxmples
1 'hot' 'the water is hot\n she's hot\n'
2 'warm' '...'
3 'cold' '...'
4 'freezing' '...'

Your synonym table:

SynID WordID1 WordID2
1 1 2
2 3 4

And your antonym table:

AntID WordID1 WordID2
1 1 3
2 1 4
3 2 3
4 2 4

• beoliver: why are there WordID1 & WordID2 when both reference word(ValID)?
• beoliver: as then surely you can only reference only two words at a time? with wordID1 and wordID2
• Maehler: Well, in each entry in those tables you will have either one synonym or one antonym. They will consist of two words that are either synonymous or antonymous to each other. To have more synonyms or antonyms, just add more items to these tables.
• beoliver: Yep, it just clicked. Thanks for that!