This is a 'best practices' question. I am replacing an application that used the Unidata (Pick like) database. The application itself is a messaging process, which is not a problem, neither is the setup of the table. My question is of an index nature. In Unidata, there is a small table for 'pending' messages, and a very large table for 'sent' messages.
My question is: could I combine the two tables, using a Boolean field that would be equal to 1 when a message was pending (not yet sent), then changed 0 when sent. Could I create an index on this field (column), but only when the field is greater than 0? And would this be an efficient way to do this? Or is it better to continue to have two tables to do this? My main concern is to make sure the 'pending' messages can be searched efficiently.
Thank you for your assistance,