Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2005
    Posts
    63

    Unanswered: Language-specific status ID

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    63
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by screenmates
    If I use 0/1 (for English) ...
    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

    Quote Originally Posted by screenmates
    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.
    that's it, that is the way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2005
    Posts
    63
    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...
    Last edited by screenmates; 04-03-06 at 13:54.

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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...

    Code:
    users
    --------------
    ...
    userid,
    languageid,
    ...
    
    transactions
    --------------
    ...
    statusid,
    ...
    
    statuses
    --------------
    statusid,
    code,
    languageid,
    description
    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:
    Code:
    users
    --------------
    ...
    userid,
    languageid,
    ...
    
    transactions
    --------------
    ...
    status_code,
    ...
    
    statuses
    --------------
    status_code,
    languageid,
    description
    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.

  7. #7
    Join Date
    Jul 2005
    Posts
    63
    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...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jfulton, thanks for stepping in, that looks a lot nicer explanation than i would've come up with

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    no prob. and thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •