Localized Database Schema - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Localized Database Schema

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2014-03-17 : 12:13:28

Hello,

I am creating an application with localization and SQL table.

I am wondering which approach should I use to create my tables.

OPTION 1

One table (Posts) for common data and another (Posts_Localized) for each culture.

create table dbo.Posts
(
Id int identity not null,
Created datetime not null
);

create table dbo.Posts_Localized
(
Id int not null,
Culture int not null,
Title nvarchar (200) not null
);

OPTION 2

One table (Posts) for all data ... Each record has a KEY.

Versions EN, PT, FR will share a KEY because are different versions of the same post.

create table dbo.Posts
(
Id int identity not null,
Created datetime not null,
Culture int not null,
Title nvarchar (200) not null,
[Key] uniqueidentifier not null
);

I see advantages on the second one because:

1. There will be less joins;

2. If I decide that a column might change from common to not common I will not need to change the database Schema ...

What do you think?

Thank You,
Miguel

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-17 : 13:17:29

I'd personally go with Option #1. It also makes it easier to have a default. Be that I mean, if you have a situation where the user wants it in FR and there is no data for FR, you can easily default everything to EN or whichever language makes sense. Unless you plan on having all the languages at one time, then you run into different issues trying to enforce that.

Also, don't be worried about the number of joins. *IF* it should ever be an issue, then you can revisit it. But, in general, normalize, normalize, normalize.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2014-03-17 : 16:03:30

And should I have a cultures table or can I have something like:


  create table dbo.Posts 
  (
    Id int identity not null,
    Created datetime not null
  );

  create table dbo.Posts_Localized
  (
    Id int not null,
    [Culture] int not null 
      constraint CK_Culture check ([Culture] in ('1', '2', '3')),
    Title nvarchar (200) not null
  )

Then in my .NET application Culture is mapped to an ENUM.

I am not sure if this is ok or if I need to have a lookup table for cultures ...

What do you think?

Thank You,
Miguel

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-17 : 17:13:07

I'd have a culture/language table. You don't need to use integer values for the key either. I'm not saying you shouldn't, but EN, FR, etc, seems like perfectly acceptable key values to me. In fact, I would use ISO codes. Something like:
http://www.loc.gov/standards/iso639-2/php/code_list.php

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources