I have a transaction table with tinyint StatusID to indicate the row status:
The status table has columns:
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
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?
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:
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.
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.
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.
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?
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:
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.