Multiple tables for multi language? 2

[Nander]

Ok, I think this is a tricky one but who knows...

In an effort to create a multilanguage database, I set up the following.

I have a table with ids, called Items:

id     other columns
====================
  1    ....
  2    ....
  3    ....

Now, I have another table with values, called Values:

id    Language   other columns
==============================
  1   english    .....
  1   dutch      .....
  2   dutch      .....
  3   english    .....

So, as you can see, item #1 is available in english and dutch, item #2 in dutch only and item #3 in english only.

Now I have the following questions:
1. is this efficient? Is there a better way to do it?
2. I can request all items in a certain language by using a stored procedure that accepts a parameter for Language. But what if I wanted to make a stored procedure that accepts an id and a number of language preferences? So, let's say, I can tell the stored procedure to return item #2 in english, and if english does not exist in dutch, and if dutch does not exist in german, etc?

Hope you can give me some help!

Thanks,
Nander.

 

[donutman]

I think the best solution to this depends upon the Other Column data and the application.
1) How much or how little similarity is there between an English version of the item and the Dutch version.
2) How are the items going to be "looked up". The ID column isn't a likely candidate, is it?
It's possible that the design you have is just fine the way it is, but it's also possible that the table you illustrated should be the child table of a parent table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Nander]

Hi Karl, thanks for your input.

What I like about this solution is the different languages having the same ID. It makes switching from one language to another completely transparent.

The table with ID numbers is there so that SQL Server can generate the ids itself. The ids in the second table are copied from the first, quite similar to a parent-child relation. Duplicates can exist there. The table with ids itself is hardly ever queried.

This solution works ok, allthough it doesn't always seem very efficient. The main problem is related to my second question, which I haven't been able to solve.

 

[r937]

with a stored procedure, anything's possible

pseudo-code:
select english item #2
if it doesn't exist, select dutch item #2
if it doesn't exist, select german item #2

without a stored procedure, you'd use a triple left outer self-join with the COALESCE function

rudy

http://r937.com/

SQL Consulting

 

[Nander]

Thanks, I guess I can figure that out.

I like the anything is possible point of view, so one more thing. Is there a way to make the stored procedure less static? I would like it to try a number of languages specified in a param. Because there are no list or collection param types, I suppose this might be a varchar param with a value of, say, "enduge" (english-dutch-german) or "duen" (dutch-english)? The values in my Language column are allready in 2-letter format.

 

[r937]

see Passing a list/array to an SQL Server stored procedure



rudy

http://r937.com/

SQL Consulting

 

[ncirkel]

Cool, I got all the ingredients now. Going for it ;-)
A thank-you-star for both of u!