字典应用的数据库设计 [英] database design for dictionary application

问题描述

目前我想开发移动设备的字典应用程序。字典本身使用脱机文件/数据库来翻译这个词。它只是翻译两种语言，例如英语 - 西班牙语字典。
我想起了一个简单的设计。它将是两个表：英文表和西班牙语表。
为每个表包含：

• word_id =将作为其他表的外键的ID


• word =单词


• word_description


• 对应_trans_id =其他表的id，这个单词与其他语言的翻译

而且因为这是针对移动应用，数据库使用SQLite。

表格上的字段'word'提供了每个表格的定义数据。但是，如果还有额外的数据定义，我仍然在考虑问题。因为表将按字段排序'word'，是否有任何方法放置（插入）新的记录仍然依次按字？或任何想法，使其更有效率？

解决方案

至少对于每个翻译，根据上下文有一些翻译的可能性。如果您想为两种语言做双向字典，您需要至少三个表：

 中文
 ID | WORD 
 1 | 'dictionary'
 
 GERMAN 
 ID | WORD 
 1 | 'lexikon'
 2 | 'wörterbuch'
 
 TRANSLATION_EN_DE 
 ID_EN | ID_DE 
 1 | 1 
 1 | 2 
  

前两个表包含该语言中已知的所有单词，双向映射是由第3个映射表完成。这是一个常见的n：n映射情况。
有两个表，你总是能够添加一个新的语言，你是dicitionary。如果你使用一个表，你会有一个单词的多个定义，因此没有规范的db。

你也可以将你的语言表合并成一个表通过另一列定义语言（引用语言表）。在这种情况下，您将需要一个2列索引的语言和自己的单词。

Currently I'd like to develop dictionary application for mobile device. The dictionary itself use offline file/database to translate the word. it just translates for two languages, for example english - spanish dictionary. I've a simple design in my mind. it would be two tables: English Table and Spanish Table. for each table contain of:

• word_id = the id which would be a foreign key for other table
• word = the word
• word_description
• correspond_trans_id = the id of other table which is the translation for this word to other language.

and also because of this is for mobile application, the database use SQLite.

The definition data for each table has been provided order by field 'word' on the table. However I'm still thinking the problem if there is addition for the data definition. Because the table would be order by field 'word', is there any method to put (insert) the new record still in order by word ? or any idea to make it more efficient ?

解决方案

At least it for each translation there are a few translation possibilities depending on the context. if you like to do a bidirectional dictionary for two languages you need at least three tables:

ENGLISH
ID | WORD
1  | 'dictionary'

GERMAN
ID | WORD
1  | 'lexikon'
2  | 'wörterbuch'

TRANSLATION_EN_DE
ID_EN | ID_DE
1     | 1
1     | 2

The first two tables are containing all the words that are known in that language and the bidirectional mapping is done by the 3rd mapping table. this is a common n:n mapping case. with two more tables you're always able to add a new language into you're dicitionary. If you're doing it with one table you'll have multiple definitions for a single word thus no normalized db.

you can also merge your language tables into a single table defining the words language by another column (referencing a language table). in that case you'll need a 2-column index for the language and the word itsself.

这篇关于字典应用的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！