Localized Tables
shapper
I am about to start a multilanguage web site and I need to save the
data on SQL tables.
My idea is to create something like:
create table dbo.Articles (
Id int identity not null,
Created datetime not null,
constraint PK_Articles primary key clustered(Id)
)
create table dbo.ArticlesLocalized (
Id int identity not null,
ArticleId int not null,
CultureId int not null,
Title nvarchar(200) not null,
Content nvarchar(max) not null,
constraint PK_ArticlesLocalized primary key clustered(Id)
)
create table dbo.Cultures (
Id int identity not null,
Code nvarchar(10) not null,
Name nvarchar(100) not null,
constraint PK_Cultures primary key clustered(Id)
)
My idea is to have this approach to all tables.
Articles:
Article Id and Columns common to all cultures
ArticlesLocalized (Should I name it so?):
Columns which differ from culture to culture.
Cultures (Should I name it so?):
The code can be "PT, EN, etc". Should I use pt-PT, en-US, etc?
I am asking this because in this application I will not differentiate
between en-US and en-GB. It will be the same.
Name is the description. For example: en-US = "English"; pt-PT =
"Portuguese"
So in my C# code I will have a entity named Article.
In the ArticleRepository I have a method that will be named:
GetByIdAndCulture(Int32 Id, String Culture)
I will then create the Article entity from Articles, ArticlesLocalized
and Cultures table ...
Well, this is my idea and questions. Am I planning this correctly?
Thanks,
Miguel
Erland Sommarskog
> My idea is to create something like:
>
> create table dbo.Articles (
> Id int identity not null,
> Created datetime not null,
> constraint PK_Articles primary key clustered(Id)
> )
>
> create table dbo.ArticlesLocalized (
> Id int identity not null,
> ArticleId int not null,
> CultureId int not null,
> Title nvarchar(200) not null,
> Content nvarchar(max) not null,
> constraint PK_ArticlesLocalized primary key clustered(Id)
> )
>
> create table dbo.Cultures (
> Id int identity not null,
> Code nvarchar(10) not null,
> Name nvarchar(100) not null,
> constraint PK_Cultures primary key clustered(Id)
> )
The basic structure is the same as we have in our database. The one
difference is that we also have a Title column in the main database, which
holds the name of the item in the system language. This is intended to be
a fallback if the name is missing in a certain language. Queries typically
go:
somename = coalesce(local.Title, main.Title)
...
FROM Articles main
LEFT JOIN ArticlesLocalised local ON main.Id = local.ArticleID
AND local.CultureID = @usersculture
However, there are a couple of flaws in your tables. There should be no
Id column in ArticlesLocalized, but the primary key should be
(ArticleID, CultureId). You don't intend to store multiple rows for the
same article and language, do you?
Nor should you have an Id column in the Cultures table. In this case there
exists a natural key, and that is what you should use. As for which key,
there are a couple of choices. You could use the language code "en", "pt"
etc. If you do not plan to use language variations, you should stick to
two-letter codes. Then again, you should probably leave room for the
subspecification, in case you in the future find it necessary to distinguish
between en-US and en-GB. (After all English comes in different
colo(u)rs. :-) If you go this route, the key be char(5) or varchar(5). Not
nvarchar(10) - these codes are ASCII only. And this code should also be the
key in ArtitlcesLocalised.
The other alternative is to use the LCID which is defined by Windows. This
is what we use in our system. We only use the part for the major language,
so English is language_id = 9, Swedish = 29 and so forth. But we use
smallint for this ID, so if a customer would require that we handle
variations we have space for that.
> Cultures (Should I name it so?):
Langauges seems a more natural name, but that's me.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
No.
There are ISO codes for languages, so do not invent your own.
Depending on the article's area of study, you will find an industry
standard identity (and it is never a silly IDENTITY). For example
there is the ISSN (International Standard Serial Number) for
magazines.
Do not talk to database people; talk to a librarian. This is what
library Science does. And this is why there are software packages for
managing documents that conform to all the ISO, ANSI-Z39, etc,
standards.
Iain Sharp
And if the articles are in fact generated internally and are not
published via a standards authority (as is, I strongly suspect, the
case).
Should he not use an internal surrogate key, rather than one imposed
by an external authority? (The ISSN is merely a composite surrogate
key, in that only part of the number has independant meaning, the rest
is just a counter, much like an EAN 13 barcode....)
--CELKO--
The definition of a surrogate key is that it is generated in the DB
and never exposed to the users (think indexing and hashing). I think
you meant an artificial key internal to the company. And I would
still talk to a librarian or forms control officer about a well-
defined document numbering system
>> (The ISSN is merely a composite surrogate key, in that only part of the number has independent meaning, the rest is just a counter, much like an EAN 13 barcode....) <<
The ISSN is a global industry standard, and it has a defining
authority with external verification and validation rules. Nothing
like a surrogate at all.
Take a look at company manuals these days. A lot of them have gone
away from the company internal artificial key to at least add an ISSN
and ISBN as an alternate key so that they can be put in public
libraries and databases.
Tony Rogerson
> and never exposed to the users (think indexing and hashing). I think
There you go again with your incorrect advice not applicable to this product
(MICROSOFT SQL SERVER).
Unlike other products on foreign keys there is no magic internally generated
id.
The data would need to be copied everything.
That means in Amazon - your email address would be everywhere and when it
changed oh boy what a nightmare for concurrency.
This is why we use the IDENTITY property on a column that is never exposed
to the application user - it is only ever used in the plumbing within the
application - as per Codd and Date.
Unfortunately until you accept the forum you have chosen to park and troll
(MICROSOFT SQL SERVER) then you will keep getting these fundamental design
decisions wrong and be in conflict with product best practice.
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:31952e18-8b2e-4220...@t18g2000vbj.googlegroups.com...
Tony Rogerson
Try reading this:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/11/427.aspx
Puts to bed some of the misnomers on Codd and surrogate keys.
Tony
"Iain Sharp" <ia...@pciltd.co.uk> wrote in message
news:k4gng5tdksh5ornbk...@4ax.com...
Erland Sommarskog
> There are ISO codes for languages, so do not invent your own.
Seems to me that Shapper is using ISO codes for languages, so what
are you btiching about? If you wonder about the -US thing, that is the
codes that Windows uses. That should be good enough for a standard.
But of course, you never miss the occasion to spit on people, no matter
whether there is reason for it or not.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
shapper
Ooops I didn't notice that Celko went in this topic and fight
started. :-)
As far as I know the culture code is composed of two parts:
language-COUNTRY
A few examples:
pt: Portuguese
pt-PT: Portuguese-Portugal
pt-BR: Portuguese-Brazil
en: English
en-GB: English-Great Britain
en-US: English-United States
In C# there is a way to get the full code or only the two letter code,
I mean, en, or pt.
In this project I don't need to differentiate between countries so in
my SQL table I am saving only PT, US, etc.
I have been Goggling and it seems I should use always ID (Identity) as
primary key and not the code.
Consider one day that the code changes ... Ok highly improbable in
this case.
But there are other cases where this might happen ... So maybe using
always an ID as PK might be a good approach?
Thank You,
Miguel
Erland Sommarskog
> In this project I don't need to differentiate between countries so in
> my SQL table I am saving only PT, US, etc.
That's the country code. You should use the language code in the table.
> I have been Goggling and it seems I should use always ID (Identity) as
> primary key and not the code.
Certainly this is an issue on which there is more tha one opinion-
> Consider one day that the code changes ... Ok highly improbable in
> this case.
Yes, it may change, and I certainly think that primary keys should be
immutable. But I think that common codes likes country codes or language
codes can count as immutable. And country codes can definitely change.
I know, because use ISO codes as keys for countries in our system, and
YU became CS which became SR (and MN). But those changes are implemented
with other database changes.
But as I mentioned, you can also use the same ids that Windows uses.
> But there are other cases where this might happen ... So maybe using
> always an ID as PK might be a good approach?
No. Use a surrogate when it's called for. Not as a matter of routine.
And definitely not in a junction table.