database columns vs. gui columns and sorting

Discussion in 'Java' started by ittay.dror@gmail.com, Feb 26, 2006.

  1. Guest

    Hi,

    I keep running into a problem of how to bridge how data is saved in the
    DB and how it is presented to the user, especially when sorting lists.

    Say I have a table named users. it has all the usuall columns (id, name
    etc.) and in addition a status, that can be 1 for active user, 2 for
    suspended user and 3 for deleted user (this is of course a dummy
    example, invent any enumeration you want). The user is presented with a
    list of users, and in the column "Status", the strings "Active",
    "Suspended" and "Deleted" are used.

    Now, I want to let the user sort by the status column. Since I have
    many users, I want the sorting to be done in the DB, and only part of
    the data (current page) to be returned. However, sorting by the status
    column will return Active users first, then Suspended, and only then
    Deleted. Not the order one would expect.

    I can of course solve it trivially by exchanging the meaning of '2' and
    '3' in the column. But then what will happen if I want to translate to
    other languages? Another option is to maintain a gui_status column in
    the DB, but then the point of MVC is broken, and again, what about
    translations? I can also load all rows, create records with the gui
    names instead and sort in memory, but that is very expensive.

    To make the problem more complex, sometimes several columns are
    aggregated as one column in the gui.

    So my question is whether there is a known idiom to handle such cases.

    Thank you,
    Ittay
    , Feb 26, 2006
    #1
    1. Advertising

  2. Guest

    > Hi,
    >
    > I keep running into a problem of how to bridge how data is saved in the
    > DB and how it is presented to the user, especially when sorting lists.
    >
    > Say I have a table named users. it has all the usuall columns (id, name
    > etc.) and in addition a status, that can be 1 for active user, 2 for
    > suspended user and 3 for deleted user (this is of course a dummy
    > example, invent any enumeration you want). The user is presented with a
    > list of users, and in the column "Status", the strings "Active",
    > "Suspended" and "Deleted" are used.
    >
    > Now, I want to let the user sort by the status column. Since I have
    > many users, I want the sorting to be done in the DB, and only part of
    > the data (current page) to be returned. However, sorting by the status
    > column will return Active users first, then Suspended, and only then
    > Deleted. Not the order one would expect.

    Well, the sorting order "Active->Suspended->Deleted" is not alphabetic.
    If one expected an alphabetically sorted list, you are right in saying
    that it is "not the order one would expect". But from a logical point
    of view, the order is fine. What else is could be wrong with that
    sorting oder?
    > I can of course solve it trivially by exchanging the meaning of '2' and
    > '3' in the column. But then what will happen if I want to translate to
    > other languages?

    What means "language" in this context? Java vs. C++ vs. Perl or are you
    talking about SQL? Sorry, I can´t follow you here.
    > Another option is to maintain a gui_status column in
    > the DB, but then the point of MVC is broken, and again, what about
    > translations?
    > I can also load all rows, create records with the gui
    > names instead and sort in memory, but that is very expensive.
    >
    > To make the problem more complex, sometimes several columns are
    > aggregated as one column in the gui.
    >
    > So my question is whether there is a known idiom to handle such cases.

    I assume you get your results (or your ResultsSet object) from a query.
    Wouldn´t it be possible to simply split your query into three, where
    the sequence of queries gives you the sequence of "status" columns as
    desired, and the combine the data from the three queries into one
    JTable? How do you handle the visualization of your DB data at present?
    I guess it goes from ResultSet to TableModel to JTable? Can you show us
    some code?
    BW, Piet
    , Feb 26, 2006
    #2
    1. Advertising

  3. wrote:
    > So my question is whether there is a known idiom to handle such cases.
    >

    The usual way is to create another table containing the codes and their
    translations. All queries are extended to join the detail table to the
    code table and to return the translation rather than the code.

    There are immediate benefits: when you're inserting rows you can
    validate the code against the codes table and you've solved a
    maintenance issue because adding a code doesn't need a program change:
    just add another row to the codes table.

    In your case you could add a third (sort order) column to the codes
    table. The overhead is small because it won't affect the join and the
    column will be discarded without needing to be returned as part of the
    result set.

    --
    martin@ | Martin Gregorie
    gregorie. |
    org | Zappa fan & glider pilot
    Martin Gregorie, Feb 26, 2006
    #3
  4. wrote:
    > Hi,
    >
    > I keep running into a problem of how to bridge how data is saved in the
    > DB and how it is presented to the user, especially when sorting lists.
    >
    > Say I have a table named users. it has all the usuall columns (id, name
    > etc.) and in addition a status, that can be 1 for active user, 2 for
    > suspended user and 3 for deleted user (this is of course a dummy
    > example, invent any enumeration you want). The user is presented with a
    > list of users, and in the column "Status", the strings "Active",
    > "Suspended" and "Deleted" are used.
    >
    > Now, I want to let the user sort by the status column. Since I have
    > many users, I want the sorting to be done in the DB, and only part of
    > the data (current page) to be returned. However, sorting by the status
    > column will return Active users first, then Suspended, and only then
    > Deleted. Not the order one would expect.


    WARNING: I'm not an SQL expert, so this might not make any sense.

    Have you tried a CASE expression in the ORDER BY clause of your SELECT?

    >
    > To make the problem more complex, sometimes several columns are
    > aggregated as one column in the gui.


    Again, I think the solution is to use the expression you want in the
    ORDER BY clause, rather than a simple column name.

    Patricia
    Patricia Shanahan, Feb 26, 2006
    #4
  5. Guest

    wrote:
    > > Hi,
    > >
    > > I keep running into a problem of how to bridge how data is saved in the
    > > DB and how it is presented to the user, especially when sorting lists.
    > >
    > > Say I have a table named users. it has all the usuall columns (id, name
    > > etc.) and in addition a status, that can be 1 for active user, 2 for
    > > suspended user and 3 for deleted user (this is of course a dummy
    > > example, invent any enumeration you want). The user is presented with a
    > > list of users, and in the column "Status", the strings "Active",
    > > "Suspended" and "Deleted" are used.
    > >
    > > Now, I want to let the user sort by the status column. Since I have
    > > many users, I want the sorting to be done in the DB, and only part of
    > > the data (current page) to be returned. However, sorting by the status
    > > column will return Active users first, then Suspended, and only then
    > > Deleted. Not the order one would expect.

    > Well, the sorting order "Active->Suspended->Deleted" is not alphabetic.
    > If one expected an alphabetically sorted list, you are right in saying
    > that it is "not the order one would expect". But from a logical point
    > of view, the order is fine. What else is could be wrong with that
    > sorting oder?


    that people expect alphabetic sorting.

    > > I can of course solve it trivially by exchanging the meaning of '2' and
    > > '3' in the column. But then what will happen if I want to translate to
    > > other languages?

    > What means "language" in this context? Java vs. C++ vs. Perl or are you
    > talking about SQL? Sorry, I can´t follow you here.


    other human language, say french.

    > > Another option is to maintain a gui_status column in
    > > the DB, but then the point of MVC is broken, and again, what about
    > > translations?
    > > I can also load all rows, create records with the gui
    > > names instead and sort in memory, but that is very expensive.
    > >
    > > To make the problem more complex, sometimes several columns are
    > > aggregated as one column in the gui.
    > >
    > > So my question is whether there is a known idiom to handle such cases.

    > I assume you get your results (or your ResultsSet object) from a query.
    > Wouldn´t it be possible to simply split your query into three, where
    > the sequence of queries gives you the sequence of "status" columns as
    > desired, and the combine the data from the three queries into one
    > JTable? How do you handle the visualization of your DB data at present?
    > I guess it goes from ResultSet to TableModel to JTable? Can you show us
    > some code?


    well, it's actually from hibernate to struts, but i don't think this is
    relevant here

    splitting the query into 3 sounds wrong to me. what if i have 7
    statuses?

    > BW, Piet
    , Mar 4, 2006
    #5
  6. Guest

    my concern is that this means pushing the gui (view) deep into the
    model. for example, it means that if i want to translate the
    application to another language (say french), i need to deal with the
    DB initialization also
    , Mar 4, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alex
    Replies:
    0
    Views:
    446
  2. =?Utf-8?B?RG9ydGU=?=
    Replies:
    0
    Views:
    4,567
    =?Utf-8?B?RG9ydGU=?=
    Dec 14, 2005
  3. Alex
    Replies:
    0
    Views:
    149
  4. Jason
    Replies:
    0
    Views:
    373
    Jason
    Oct 4, 2006
  5. Hal Fulton

    GUI and non-GUI data

    Hal Fulton, Aug 5, 2004, in forum: Ruby
    Replies:
    1
    Views:
    134
    Austin Ziegler
    Aug 5, 2004
Loading...

Share This Page