I have a transaction table with tinyint StatusID to indicate the row status:

Transaction.StatusID

The status table has columns:

ID
Code
LanguageID
Description

The data for the status table goes as under:


1 ACTIVE en Active
2 INACTIVE en Inactive
3 ACTIVE fr Actif
4 INACTIVE fr Inactif
5 ACTIVE de Aktiv
5 INACTIVE de Unaktiviert


Based on the user's language setting, the appropriate ID is assigned to transaction.StatusID. However, when the user's language changes, this is resulting in a no-data-found situation for existing transaction rows for the status because the select statement for the status table is:

select ID, Description
from status
where Code IN ('ACTIVE', 'INACTIVE')
and LanguageID = $languageId

If ID 1 was assigned to transaction for en/ACTIVE status and the user changes the language to fr later, there is no row with the ID of 1 for fr in the status table. The tables are InnoDB with PK/FKs.

Considering the above situation, how do I go about implementing a design for the status table that does not get impacted when the user's language changes?

TIA.

use status code 0=inactive 1=active independent of language

you do not need the ID either -- make the primary key of the status table a composite key consisting of status code and language id together

The application is multi-lingual and hence we use the Description column to display the interface strings and the data in the user's language without using gettext(). Active/Inactive values need to be shown in the user's native language. If I use 0/1 (for English), how about users of other languages? I'm trying to figure out a way to store status values in a generic way that do not get impacted when the user saves a transaction with English status codes and then switches his/her language to French where the status values are not the same and the dropdowns do not populate with data because of the above select statement on status. I was thinking of:

Code
LanguageID
Description

and store just Code column in the transaction table while the select for status includes LanguageID column, too - dynamically. But the PK is going to be Code+LanguageID so how can I link it to the transaction table with just the Code column? I guess the FK needs two columns, too. Or, should I use two tables - status (linked to transaction) and status_language (linked to status)?

Yes, there is a unique index on Code+LanguageID and I'm going blow off the ID column.

no, you may not have understood the difference i am proposing -- 0/1 are the status values in all languages

it is the description which varies by language

that's it, that is the way

I'm still a bit confused

If the PK is going to be Code+LanguageID and I link this combo to the transaction table, it means the transaction holds StatusCode along with its LanguageID. When I query the status table for the StatusCode + LanguageID combo from transaction later (after the user switches his/her language to a different one), it would fetch the description in the user's original language - not the user's current language. So, while querying the status table, do I ignore the LanguageID stored in the transaction table and just use the user's current language dynamically, instead? Seems like an idea but then, what's the purpose of storing the LanguageID in the transaction table? Just for the FK purpose? Is there any way I can do away with storing the LanguageID in the transaction table?

I've just done some testing with FKs and using phpMyAdmin, I was able to create an FK only on StatusCode (single) column in the transaction table while the PK in the status table is StatusCode+LanguageID combo. I guess the FK will still use the PK's index because StatusCode is listed as the first column in the index for status. Is this the right way to go? As long as I don't have to store 2 columns as FK in the transaction table, I'm fine with having a combo PK and this approach. It's just way too many links to carry around on FKs.

Thanks much...

Sounds like there are some important details about how your database is structured that are being left out...

I'm assuming, this is what you have...

What you may want to consider doing, is using the composite key described before for your status table and get rid of statusid. Then change transactions.statusid to transactions.status_code. Now, when you want to get the display name for a transaction status, use user.languageid and transaction.status_code to create the key for the statuses table.

A transaction should know nothing about the language its status is in. It should only know if it is active, inactive, etc. The language only matters to the user.

So a step in the right direction would look something like:
with
transactions.status_code (FK) -> statuses.status_code
users.languageid (FK) -> statuses.langaugeid
and those two fields making up the primary key of the statuses table.

Does that help at all...???

P.S. you may want to consider reworking this part of your database design.

Yes, please! I've already re-worked my database design. I was asking if it was ok to link just status.StatusCode to transaction as FK instead of the entire PK of the status table and now it is very clear.

Thanks much to all for your time...

jfulton, thanks for stepping in, that looks a lot nicer explanation than i would've come up with

no prob. and thanks.