• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Follow up, table design dictionary

This question is based on the result of http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24125835.html, 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,
0
dingir
Asked:
dingir
  • 3
  • 2
1 Solution
 
Mark WillsTopic AdvisorCommented:
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

0
 
dingirAuthor Commented:
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...
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
dingirAuthor Commented:
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?

0
 
Mark WillsTopic AdvisorCommented:
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.

Cheers,
Mark Wills

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now