SQL multiple primary keys - localization

I am trying to implement some localization in my database.

It looks something like this (prefixes only for clarification)

tbl-Categories

ID

Language

Name

tbl-Articles

ID

CategoryID

Now, in my tbl-Categories, I want to have primary keys spanning ID and language, so that every combination of ID and language is unique. In tbl-Articles I would like a foreign key to reference ID in categories, but not Language, since I do not want to bind an article to a certain language, only category.

Of course开发者_如何学编程, I cannot add a foreign key to part of the primary key. I also cannot have the primary key only on the ID of categories, since then there can only be one language. Having no primary keys disables foreign keys altogether, and that is also not a great solution.

Do you have any ideas how I can solve this in an elegant fashion?

Thanks.

Given the scenario you need to have one to many relationship established between Category and Language. Create 3 tables:

1. Category with CategoryID and Name as columns
2. Language with LanguageID and Name as Columns
3. CategoryLanguage with CategoryLanguageId, CategoryID and LanguageID (create a composite primary key on CategoryId and LanguageId which establishes uniqueness)

You dont have to do anything on the Articles table since ID and CategoryId establishes that an article can be in one of the category but not dependant on language.

HTH