3

I am a coder, not a database admin, so my question may be a little obvious to some. I just cannot find the correct search term to find the answer.

I'm trying to create a database structure for both currency and language management. I'm thinking of following structure:

Language

  • LanguageId int not null primary key
  • iso2 char(2) not null

For example:

1, EN

2, SV

Then I need to store the name of the language as well, which should be translatable into other languages. For this, I have a table:

Language_Text

  • LanguageId int not null
  • LanguageCulture char(5) not null
  • LanguageName varchar(20) not null
  • Primary Key(LanguageId, LanguageCulture)

For example:

1, en-US, English

1, sv-SE, Engelska

2, en-US, Swedish

2, sv-SE, Svenska

The issue comes when setting up keys and relationships. In Language there is only 1 key, while in Language_Texts there is an aggregate key. I would like a relation between the two tables, but having problems with this due to difference in keys.

I want to be able to get all various texts for a specific language, as well as query to get a specific culture if need be.

What is the best practice to use in this case?

Thanks in advance for any help in this matter!

Best regards

Peter Kindberg

  • 1
    How many translations per language do you want to store for each name? One or more? Joke example, but in the en-AU culture, you could name the en-GB culture "British English" (formal) or "Pommy" (slang). Vice versa, you could say "Australian English" or "Aussie". What else do you want to store translations of? – Iain Samuel McLean Elder Dec 31 '13 at 13:14
  • Can you add an example with two cultures sharing the same language, such as en-GB and en-US, or sv-SE and sv-FI? What does the culture (combination of language and country) represent in your model? It sounds like you want to store the name of every language in every language. Not sure why you need to store a culture code as well. – Iain Samuel McLean Elder Jan 2 '14 at 21:19
0

If I understand your requirements correctly....

I would just use the natural key, LanguageCode-CultureCode ("en-US," for example). It's small enough. (I'm using the entire "en-US" as the primary key to differentiate it from "en-GB," for example.)

CREATE TABLE [dbo].[Language](
    [Language] [char](2) NOT NULL,
    [Culture] [char](2) NOT NULL,
    [LanguageCode]  AS (([Language]+'-')+[Culture]) PERSISTED NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED 
(
    [Language] ASC,
    [Culture] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [LanguageCode] UNIQUE NONCLUSTERED 
(
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Language_Text](
    [LanguageID] [varchar](5) NOT NULL,
    [LanguageCode] [varchar](5) NOT NULL,
    [LanguageName] [nvarchar](20) NULL,
 CONSTRAINT [PK_Language_Text] PRIMARY KEY CLUSTERED 
(
    [LanguageID] ASC,
    [LanguageCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Language_Text]  WITH CHECK ADD  CONSTRAINT [FK_Language_Text_Language] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[Language] ([LanguageCode])
GO

ALTER TABLE [dbo].[Language_Text] CHECK CONSTRAINT [FK_Language_Text_Language]
GO

This should allow you to get all articles in en or sv, and you can also query for en-US or sv-SE. Presumably, although it wasn't in your example, you could also query for en-CA, fr-CA, en, fr, or CA.

Edit--I'm sorry, you're right, no search by Culture in my old code. Revamped above, sorry. Here's a sample of content:

Language:

Language-Culture-LanguageCode
en  US  en-US
sv  SE  sv-SE

Language_Text:

LanguageID-LanguageCode-LanguageName
en-US   en-US   English
en-US   sv-SE   Engelska
sv-SE   en-US   Swedish
sv-SE   sv-SE   Svenska

Searching by Culture (Canada):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Culture = 'CA')

Searching by Language (French):

SELECT        test.dbo.Language_Text.LanguageID, test.dbo.Language_Text.LanguageCode, test.dbo.Language_Text.LanguageName
FROM            test.dbo.Language_Text INNER JOIN
                         test.dbo.Language ON test.dbo.Language_Text.LanguageID = test.dbo.Language.LanguageCode
WHERE        (test.dbo.Language.Language = 'fr')

Searching by LanguageCode (Swedish):

SELECT LanguageName
  FROM [test].[dbo].[Language_Text]
  where (LanguageID = 'sv-SE')
  • Doesn't look like you can query for "en-US", or any culture code, without splitting it into language code and country code first. Is that what you intended? Could you show some example queries? – Iain Samuel McLean Elder Dec 31 '13 at 13:21
  • Ha, I cannot leave it alone. I created a computed column on Language for "en-US" and had headed here to update when I saw your comment. Will do so now. – Katherine Villyard Dec 31 '13 at 15:35
  • 1
    Thank you all for your answers. It solves the issues I'm facing, just creating a basic way of handling languages. I basically needed just that, and a solution to link properly. This solution provides just that. Thanks Katherine and Iain. – Peter Kindberg Jan 4 '14 at 15:44
  • @PeterKindberg Glad this helped you. A small thing: The column names are confusing if you follow the ISO terminology. ISO Language codes are lower 2-char (en), country codes are upper 2-char (US), culture codes are "language-country" codes (en-US). – Iain Samuel McLean Elder Jan 5 '14 at 17:43
  • Thanks Iain, yes, I'm having this in my consideration :) Thanks again for the help! – Peter Kindberg Jan 6 '14 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.