locked
DB design of language site RRS feed

  • Question

  • Hello everyone,

    Assume that I want to develop a language site. A site that is supported by multiple languages, but also is a learning language site. This means that I need to setup a database that makes it easy to maintain for example a list of english and spanish words, but all languages should be able to be added to this database.

    So i have been thinking the meaning of the word "dog" is in every language the same. So you actually need only one table with the key dog and the translations of it. But then again I dont have big lists that can provide this data so I or my users need to add it them selves. Therefore it could be handy to seperate the lists to be able to download some lists of words online. The last option is only possible when I keep the translation between two languages seperated, but is that smart..

    What is the best way to design such a database?

    dbo.Language
    languageId
    nameLanguage

    dbo.Words
    wordsId
    languageId
    matchingWordsId
    word

    Greetings,

    Spacelama

    Tuesday, July 16, 2013 6:12 AM

Answers

  • Do you want to provide a translation between languages? Your design looks OK, just wonder what is matchingWordsId for?

    Perhaps  you would need a table Language_Translation which contains fromLangid, toLangid, wordidfrom,workidto...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Tuesday, July 23, 2013 8:13 AM
    • Marked as answer by Fanny Liu Wednesday, July 24, 2013 7:10 AM
    Tuesday, July 16, 2013 7:37 AM
  • I think you need to explore your idea some more.  Forget about the database.  Construct some use cases, dive into the purpose of such a system.  When I see "...want to develop a language site", I cringe because that means very little.  Likewise, "download a list of words".  It is never good to start a project with a vague description of what you intend to do. 

    In addition, do you have experience in teaching foreign languages?  If not, then I think it will be extremely difficult for you to be successful.  On the other hand, perhaps this is just a learning experience.  If it is the latter, then stop worrying about the schema and just do it.  As you encounter and solve each issue, you will gradually arrive at a workable schema (or at least discover just how difficult it is to do "translation").

    One last comment.  Consider the names of your tables - Language vs Words.  One singular, one plural.  I hate inconsistency.  Also, stop assuming that every table must have an identity column as the primary key.  An surrogate key is something you add when it is needed.  At this phase, nothing like this is NEEDED. Focus on data analysis - identify the entities and their attributes. 

    • Proposed as answer by Fanny Liu Tuesday, July 23, 2013 8:13 AM
    • Marked as answer by Fanny Liu Wednesday, July 24, 2013 7:10 AM
    Tuesday, July 16, 2013 1:44 PM

All replies

  • Do you want to provide a translation between languages? Your design looks OK, just wonder what is matchingWordsId for?

    Perhaps  you would need a table Language_Translation which contains fromLangid, toLangid, wordidfrom,workidto...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed as answer by Fanny Liu Tuesday, July 23, 2013 8:13 AM
    • Marked as answer by Fanny Liu Wednesday, July 24, 2013 7:10 AM
    Tuesday, July 16, 2013 7:37 AM
  • It should be a learning site between multiple languages. SO you can select your base language and then select a language that you want to learn.

    Ofcourse there are thousands of them, but just want to try to make a DB design like that. What is my best option?

    Tuesday, July 16, 2013 7:42 AM
  • Again, your design posted above looks OK, how people learn language? It provides a word and  the program returns translation? 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 16, 2013 8:47 AM
  • I think you need to explore your idea some more.  Forget about the database.  Construct some use cases, dive into the purpose of such a system.  When I see "...want to develop a language site", I cringe because that means very little.  Likewise, "download a list of words".  It is never good to start a project with a vague description of what you intend to do. 

    In addition, do you have experience in teaching foreign languages?  If not, then I think it will be extremely difficult for you to be successful.  On the other hand, perhaps this is just a learning experience.  If it is the latter, then stop worrying about the schema and just do it.  As you encounter and solve each issue, you will gradually arrive at a workable schema (or at least discover just how difficult it is to do "translation").

    One last comment.  Consider the names of your tables - Language vs Words.  One singular, one plural.  I hate inconsistency.  Also, stop assuming that every table must have an identity column as the primary key.  An surrogate key is something you add when it is needed.  At this phase, nothing like this is NEEDED. Focus on data analysis - identify the entities and their attributes. 

    • Proposed as answer by Fanny Liu Tuesday, July 23, 2013 8:13 AM
    • Marked as answer by Fanny Liu Wednesday, July 24, 2013 7:10 AM
    Tuesday, July 16, 2013 1:44 PM