SQL multiple primary keys - localization

Question

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.

Answer 1

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