Follow up, table design dictionary

Posted on 2009-02-14
Last Modified: 2012-05-06
This question is based on the result of, where I got an follow up. A dictionary with many words, needs of course to be grouped.

This dictionary needs an extra level. A grouping level.

* View available Language
* View available groups (Which also needs to exist on all languages), 6 languages.
* View all words based on the selected language and group

Thank's in advanced,
Question by:dingir
    LVL 51

    Expert Comment

    by:Mark Wills
    Hmmm... would have taken a slightly different approach in having aliases so if your search was Monkey, then you could display in any laguage, and find monkey via other languages.  Same would apply to group, otherwise you are trying to update / change multiple instances of the same "key" but different values.

    -- first up let's create some temp table so we can test - these would be replaced with your real tables...
    create table  #groups (g_ID int identity primary key clustered, GroupName nvarchar(50))
    create table  #words (w_ID int identity primary key clustered, GroupID int, wordText nvarchar(50))
    -- aliases used to be known as language, but being more generic (see below), more like a multilingual thesaurus.
    -- if more convenient, can do a global change of alias to language.
    create table  #Aliases (a_ID int identity primary key clustered, AliasName nvarchar(50))
    create table  #group_aliases (ga_ID int identity primary key clustered, GroupID int, AliasID int, GroupAlias nvarchar(50))
    create table  #word_aliases (wa_ID int identity primary key clustered, WordID int, AliasID int, WordAlias nvarchar(50))
    -- in the above, should really have the id in the detail table refer to "parent"
    -- groups
    insert #groups (groupname) VALUES ('Primates')
    -- words
    insert #words (groupid, wordtext) VALUES (1,'Monkey')
    -- aliases also known as languages
    insert #Aliases (AliasName) VALUES ('English')
    insert #Aliases (AliasName) VALUES ('French')
    insert #Aliases (AliasName) VALUES ('German')
    -- alternate names for groups
    insert #group_aliases (groupid, aliasid, groupalias) values (1,1,'Primates')
    insert #group_aliases (groupid, aliasid, groupalias) values (1,2,'Primate')
    insert #group_aliases (groupid, aliasid, groupalias) values (1,3,'Primat')
    -- alternate names for words
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,1,'Monkey')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,2,'Singe')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,3,'Affe')
    -- structured query showing relationships, but a lot of different ways to build the query
    select g.groupname,w.wordtext,a.aliasname, ga.groupalias, wa.wordalias
    from #groups g
    inner join #words w on w.groupID = g.g_id
    left outer join (#aliases a inner join #group_aliases ga on ga.aliasid = a.a_id
                                inner join #word_aliases wa on wa.aliasid = a.a_id) on ga.groupid = g.g_id and wa.wordid = w.w_id 
    -- now let's structure a new relationship for word aliases
    declare @a_id int
    insert #Aliases (AliasName) VALUES ('Thesaurus')
    set @a_id = scope_identity()
    insert #group_aliases (groupid, aliasid, groupalias) values (1,@a_id,'English Thesaurus')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,@a_id,'Monkeys')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,@a_id,'Ape')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,@a_id,'Primate')
    insert #word_aliases (wordid, aliasid, wordalias) VALUES (1,@a_id,'Gorrilla')
    -- now let's look at a slightly different query structure where a group does not have to belong to any particular alias
    select g.groupname,w.wordtext,a.aliasname, wa.wordalias
    from #groups g
    inner join #words w on w.groupID = g.g_id
    inner join #word_aliases wa on wa.wordid = w.w_id
    inner join #aliases a on a.a_id = wa.aliasid

    Open in new window

    LVL 1

    Author Comment

    yeah I see that my solution was going over my head when I came to the CMS part of it. too bad with my available time. I need to start over with an simplier version and forget the language part. Just a "group" "words" and then the translation...
    LVL 51

    Expert Comment

    by:Mark Wills
    Yeah, think so... You could still use the above, just need to decide if you are making it mandatory to have same Alias's at the group before you can have them at the word level...
    LVL 1

    Author Comment

    Thank's. I'm sure it works. I'm now finished with my solution and are happy with it (except that I have a coming nightmare importing the excel data files where the translations are stored in fields). I will however use ur design later. My solution is now (in short) something like this,

    - lang_id
    - lang_name

    - group_uid
    - group_name
    - group_lang_id
    - group_id

    - word_id
    - word_lang_id
    - word_group_id
    - word_text

    Which let the visitor to select a language, select a group and then gets a list of words related to that group and language. When clicking a word, the user gots a translation for that word on all available language in the dictionary_language. The word and menus is always showed up with the correct choosed local language.

    For the CMS part the Add Group query creates the new group for all available languages. When adding a word, that word are being created for that group in every available language. I can also create and delete a new/existing language with just a click. When the CMS-user clicked a group_name, the user gots a editable gridview with the translation to all available languages. The same idea is applied when clicking a word.

    Am I still doing something uneccesary complicated here?

    LVL 51

    Accepted Solution

    No, sounds pretty good actually. Always a simpler / cleaner approach is to be considered first and foremost, especially if you are controlling the logic of adding the appropriate entries each time.

    One question, what happens if I want to add a new laguage later on ? Does that cascade through all the pieces of information, or is that a static list - which I guess it would need to be because selecting a word will show all the translations - ie pre-populated.

    Only one comment : Bit curious as to group_uid and group_id - would have thought just group_id at the top.

    Mark Wills


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL 2014 and VB 2015 4 39
    SQL Server DatePart HOUR 6 27
    Modifying SQL 2008/2012 PARTITIONS 3 39
    Sql query 12 56
    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now