Justin (ng@NO_SPAMmari timeNO_SPAMsour ce.ca) writes:
Table: regions
id name abbreviation
1 United States US
Table: locales
id locale
1 en_US
2 fr_CA
Table: TokenKeys
id key
1 db.regions.name
2 db.regions.abbr eviation
Table: TokenValues
id keyid value localeid
1 1 Etas Unis 2
2 2 EU 2
The old sql was simply this:
select name, abbreviation from regions
which returns:
United States, US
But the new sql needs to link in the localized data from the tokeykeys
and tokenvalues tables using the localeid... I?m trying to figure out
what the sql statement would look like to return this:
Etats Unis, EU (This is supposed to be the French version)
SELECT name = tvn.value, abbr = tva.value
FROM (TokenValues tvn
JOIN TokenKeys tkn ON tvn.keyid = tkn.id
AND tkn.key = 'db.regions.nam e')
CROSS JOIN (TokenValues tva
JOIN TokenKeuys tkn ON tva.keyid = tkn.id
AND tva.key = 'db.regions.abb rivation')
WHERE tvn.localeid = @localeid
AND tvn.localeid = @localeid
This is untested as you did not provide the input as CREATE TABLE and
INSERT statements, and I'm not typing those myself.
The schema is certainly not appealing. An initial comment is that
TokenValues.id does not seem to serve any use. If I understand things
correctly (keyid, location) is the natural primary key in that table.
We have a lot of name tables in our database. The base table looks
something like this:
CREATE TABLE langauges(lngid smallint NOT NULL,
lngname varchar(30) NOT NULL,
lngcode char(2) NOT NULL,
CONSTRAINT pk_lngid PRIMARY KEY (lngid),
CONSTRAINT pk_lngcode UNIQUE (lngcode))
The language ID here is the major language id Windows, so 9 is English,
29 is Swedish. The language code is the two letter code from an ISO
standard of which the number escapes me right now. "en" for English,
"sv" for Swedish are two examples.
The base table like "regions" looks like this:
CREATE TABLE regions (regid smallint NOT NULL,
regname varchar(30) NOT NULL,
regcode char(3) NOT NULL,
...
CONSTRAINT pk_regions PRIMARY KEY (regid))
(Regions in our case is a user-defined amalgation of countries. The
code is also user-defined.)
There there is a table regionnames:
CREATE TABLE regionnames (regid smallint NOT NULL,
lngid smallint NOT NULL,
regname varchar(30) NOT NULL,
regcode char(3) NOT NULL,
CONSTRAINT pk_regnames PRIMARY KEY (regid, lngid),
CONSTRINAT fk_regid FOREIGN KEY (regid)
REFERENCES regions(regid),
CONSTRINAT fk_lngid FOREIGN KEY (lngid)
REFERENCES languages(lngid ))
A fairly straightforward design. The reader may note that regname and
regcode are both in the main table and in the name table. In our system
there is always a "home language", that is the default language for
the system. Some sites have Swedish, others Finnish etc. The names in
the home languages are duplicated so that they are both in the name
tabel and in the main table. To get a list of names in the user's
language we say:
SELECT r.regid, regname = coalesce(n.regn ame, r.regname)
FROM regions r
LEFT JOIN regionnames n ON r.regid = n.regid
AND n.lngid = @lngid
That is, the "home language" acts as fallback, in the case that there
are no values in the user's language.
That is, of course a situation you may to consider in your complicated
design that I cannot say that I don't really like. Not the least the
part where you have to reference to column names as strings. That makes
the code very sensitive to misspellings.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
