Single-table-inheritance or two tables?

Question

Hi,

Suppose I have a table with the following columns (a list of words):

word: varchar
contributor: integer (FK)

Now, suppose I wanted to have translations for each "word". What would be best? Having a second table?

word: integer (FK)
translation: varchar
contributor: integer (FK)
lang: integer (FK)

Or all in the same table?

word: varchar
translation_for: integer (FK - to the same table)
contributor: integer (FK)
lang: integer (FK)

Suppose two scenarios, (1) where I need to pull a translated word along with the original word it was translated from, (2) where I need to pull only the translated word. On both scenarios, I'd be using the "original" words far more heavily (both SELECTing and UPDATEing/INSERTing).

So, what approach would be best for each scenario, or in general? I'm inclined towards the first approach, since then my "default" SELECTs won't have to be qualified by the lang column. What do you think?

Thanks!

Answer 1

Since you seem to want a "word" to have an identity separate from a "translated word", then 1 think the 1st option meets your needs better.

Normally, I'd think a design more like:

PK (Key (varchar), Lang (FK)), Word (nvarchar)

would be more appropriate - where you only refer to text by it's Key and Lang value. But, it looks like you're replacing Key with the "original word" - so that makes it slightly different.

Answer 2

I think you'll hurt later if you want to add a language later if you don't normalize the database now. Have a word table where the word is in the default language. It has an ID. Have a language table with an ID (i.e. Spanish, 2), and a translation table that has the word ID, the language ID, and finally the actual word in that language. This is the linked table.

Use a view for your queries, but for inserts and updates, depending on your DBMS, you may need hard queries.

This is just assuming you're trying to provide localization, and thus are likely to add more languages later. Doing it this way is easier than having to modify your database to add columns every time you add a language. If you really only need ONE translation and highly doubt you'll ever need another, then just adding the one column would be fine.