database columns vs. gui columns and sorting

[ittay.dror]

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

 

[pit.grinja]

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

 

[Martin Gregorie]

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.

 

[Patricia Shanahan]

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

 

[ittay.dror]

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.

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.

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?

 

[ittay.dror]

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