I'm writing rather simple database application where user should be able either to work with the whole dataset, or to manually select certain records to work with and leave in the current grid view only these records, until view is reset or session is closed. Consider the following scenarios:
a) User has a grid that shows rather large table (100-500 thousands of records). He can select some records that he is specifically interested in and by click of the button leave only these records in the grid
b) Opposite case: user has a grid with large table, he selects several records and click another button to omit them from the current view
If you ever dealt with ACT! CRM, that scenarios should seem familiar.
So, what is the best way to achieve it?
I can think of 2 possible approaches:
- creating a temporary table to keep track of user selection. When user is choosing to "include" a record, its ID is inserted into that temporary table; when he omit a record, its record's ID is actually deleted. Something like:
// select all records into WORKSET, see the note below SELECT ID FROM CONTACTS INTO TEMP WORKSET; .... // user deletes unnecessary records from WORKSET .... SELECT * FROM CONTACTS WHERE ID IN (SELECT ID FROM WORKSET);
- to simulate similar behavior using permanent table and generated unique session id as a group key:
// select all records into WORKSET, see the note below INSERT INTO WORKSETS ( SESSION_ID, CONTACT_ID) SELECT "<SessionId>", ID FROM CONTACTS; .... // user deletes unnecessary records for given <SessionId> from WORKSETS .... SELECT * FROM CONTACTS WHERE ID IN (SELECT CONTACT_ID FROM WORKSETS WHERE SESSINO_ID = "<SessionId>"); ... // after everything is done DELETE FROM WORKSETS WHERE SESSION_ID = "<SessionId>";
Note: Of course, both of these approaches can be smart enough to choose "inclusive" (WHERE ID IN) or "exclusive" (WHERE ID NOT IN) policy depending on number of selected records and table size, to handle both scenarios better.
Did I missed an obvious third solution? If not, then which of these two approaches is preferable? Using temporary table seems easier, since all of the housekeeping is done by the database server, however, I'm not sure how well server will handle such a big temporary tables when the number of users will increase.