Create tables for a dictionary?

Question

I'm making a dictionary database in SQLite and need some tips. My SQL is a bit rusty ;) Would you consider this good SQL? It's done with Java JDBC.

This is for creating the tables.

CREATE TABLE word (
id INTEGER,
entry STRING, 
pos STRING
);

CREATE TABLE translation (
word_id INTEGER REFERENCES word(id), 
entry STRING
);

And when filling with data i give each word a number (id) and that words translations get the same number as word_id. What would be the best way of pulling out translations for a specific word?

Answer 1

I'd have a third table for the relationship between word and translation

CREATE TABLE word_translation (
word_id INTEGER, 
translation_id INTEGER
);

Than on the translation table, simply have translation_id and entry.

That way you can index your translations and words by making the id on the tables primary keys.

This also helps on your queries, as you can then have many words with many translations, and only one entry point

Answer 2

What if you instead had a "word concept" identifier and a language field, then you could make it work for multiple languages:

CREATE TABLE word (
  id INTEGER,
  entry STRING,
  language String,
  word_concept_id INTEGER,
  pos STRING
);

Then your translation query is

SELECT wr.entry FROM word wl, word wr 
WHERE wl.entry='DOOR' AND 
  wl.language='EN' AND
  wl.word_concept_id=wr.concept_id AND
  wr.language='CZ'

You would just have to figure out a scheme from the word_concept_id field (which may be just to seed with incrementing values as you add terms in the preferred language).

Answer 3

word_id INTEGER REFERENCES word(id) doesn't actually create a foreign key on MySQL. Even if you're using InnoDB, it requires an explicit FOREIGN KEY declaration.

Also, I'd probably use an autonumber for IDs for both tables, plus mark then as primary keys.

So, taking both of those into account, plus mpacona's note about multiple translations needing a many-to-many relationship:

CREATE TABLE word (
word_id UNSIGNED INTEGER AUTOINCREMENT PRIMARY KEY,
entry TEXT, 
pos VARCHAR(50)
);

CREATE TABLE translation (
translation_id UNSIGNED INTEGER AUTOINCREMENT PRIMARY KEY,
word_id UNSIGNED INTEGER,
lang VARCHAR(5),
entry TEXT
);

CREATE TABLE word_translation (
word_id UNSIGNED INTEGER, 
translation_id UNSIGNED INTEGER,
PRIMARY KEY (word_id, translation_id),
INDEX (word_id),
INDEX (translation_id),
FOREIGN KEY fk_word_id (word_id) REFERENCES word(id),
FOREIGN KEY fk_translation_id (translation_id) REFERENCES translation(id)
);

Edit: I wasn't sure what pos was, so I limited it to 50 characters. You may also need to adjust TEXT to one of its larger variants if you need more than 32k characters.

Updated lang to 5 characters to support en-us style syntax.