Follow up, table design dictionary

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

Select allOpen in new window

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...

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...

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,

table_language
- lang_id
- lang_name

table_groups
- group_uid
- group_name
- group_lang_id
- group_id

table_words
- 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?