Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > database columns vs. gui columns and sorting

Reply
Thread Tools

database columns vs. gui columns and sorting

 
 
ittay.dror@gmail.com
Guest
Posts: n/a
 
      02-26-2006
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

 
Reply With Quote
 
 
 
 
pit.grinja@gmx.de
Guest
Posts: n/a
 
      02-26-2006
> 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

 
Reply With Quote
 
 
 
 
Martin Gregorie
Guest
Posts: n/a
 
      02-26-2006
http://www.velocityreviews.com/forums/(E-Mail Removed) 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
 
Reply With Quote
 
Patricia Shanahan
Guest
Posts: n/a
 
      02-26-2006
(E-Mail Removed) 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
 
Reply With Quote
 
ittay.dror@gmail.com
Guest
Posts: n/a
 
      03-04-2006

(E-Mail Removed) 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


 
Reply With Quote
 
ittay.dror@gmail.com
Guest
Posts: n/a
 
      03-04-2006
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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
fired event Sorting which wasn't handled - sorting and SelectedIndexChanged Jason ASP .Net Web Controls 0 10-04-2006 02:19 PM
GUI and non-GUI data Hal Fulton Ruby 1 08-05-2004 08:42 PM
Ascending and Descending Sorting of Columns in an ASP.NET DataGrid... Alex ASP .Net 0 10-06-2003 04:52 PM



Advertisments