1

I have a table with offers where I perform typical CRUD actions.

Each offer can has one out of the following statuses: active, deleted, archived, withdrawn, etc.

Most actions I perform only for active offers, so is it a good idea to move offers with other statuses to other tables, like offers_archived, offers_withdrawn?

  • 2
    If your question applies to a relational database built on a certain SQL database management system, then you might be interested in implementing temporal capabilities, as detailed in this answer. Although, as per your description of the scenario at hand (which seems to imply keeping track of the status of a certain type of entity), you may find of help the deliberations contained in this series of posts. – MDCCL Sep 12 '17 at 17:59
  • Really, this will depend a lot on how the data is used. You say most actions you take only involve active offers. Are you the primary/sole user of the table in question? If not, how would this impact how others use the table? What statuses have the highest record counts? (if almost all records are active, then this would have minimal impact) Would this require changes to any existing applications, reports, etc? – RDFozz Sep 12 '17 at 21:45
3

No.

Data has a place to be based on what it is. Moving it somewhere else such as to an archive table or a deleted table is an implementation detail -- a workaround of sorts for performance.

Some databases, like PostgreSQL 10, can gracefully do this under the hood with List Partioning. In doing so, the "deleted" values are stored in a different partition (another distinct table is made available to the user), but it doesn't get in the way.

CREATE TABLE offers (...) PARTITION BY LIST (status);
CREATE TABLE offers_deleted PARTITION OF offers
  FOR VALUES IN ('deleted');

Now you could query offers WHERE status='deleted', and under the hood it would focus on just that one partition. Remove that predicate, and you'd query all of the partitions of offers. Or, you could query offers_deleted directly.

Even there though, my advice would generally be it's bad practice to move data because the status is deleted or because it's archived, unless performance demands it. Then you don't do it because you want to, you do it because you have to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.