Introducing "status" column to an SQL table changes its definition

Moving people to dead_people rarely makes sense. It is a form of an archiving cleanup, so the original table works optimal. One use is having a fast small user table for mass event checks (user rights), and a user table with full info.

With O/R mappings I would suggest discriminator columns and use database inheritance when a "status" column determines which other columns are used. With that the queries can be still maintainable.

Say you have a query on people and now introduce dead or alive people. Then the business rule determines the scope of the query: what is the goal of that piece of code.

The obvious advice is do not repeat yourself. Queries may often vary in some criteria. Measures to follow DRY can be done in JPA (java).

---

A concrete example:

A data logger receives binary message from devices. Such a message consists of bytes, containing several signals, bit groups. For the definition of such binary message parsing there is a table. The signal is an entity.

Now comes a time, where custom signals user-defined as expression of signals is introduced. Signal becomes message-specified original signal or expression based custom signal. Consider that now a custom signal can also be defined from other custom signals rather than merely the original hardware signals.

This is a semantic change Signal = HardwareSignal xor CustomSignal. A discriminator column and separate O/R mappings would do. A clean separation of corresponding fields: reference to a MessageSpecification/SignalSpecification for HardwareSignal only. No unused fields. So no fields can be accessed unintended.

That would be sufficient for a good software maintainance.

All this is not optimal, but I still have not encountered a database + O/R framework that does this all gracefully. Despite Domain Driven Development and more. But who knows.

For instance, when I need to introduce "status" to "people", I would drop the "people" table and instead create variants for "alive people" and "dead people".

You can instead create a dead_people view and a living_people view. Anyway, your code should query views and not tables to begin with.

I understand the overhead of checking everything everywhere, but with modern MVC approaches and separation of concerns, the places where a certain table or view is queried should be very limited and not widespread. Is it possible you are talking about legacy code? Been there, done that. Creating views, specially for the more widely used cases helps. What helps even more is creating functions or business objects that return the stuff you want and that are the only places where the database is queried.

It is very common to use a “status” column in tables even if it might add overhead. The added benefit is you can do “logical or soft” deletes in such cases as you might use a “status” column as representing the status of your row data.

I (almost) always include some kind of “status” or “flag” column but that may due to the types of applications I have worked on.

I have also encountered (but less so) each row being given a start and end date with the “active” record having no end date. But that will only assist you when dealing with “active record status”. The benefit here is that record history is kept in the table. An obvious drawback would be increased space consumption.

As mentioned in the comments you would also probably be best served with a lookup table that contains all your statuses and related codes. But be careful not to create a Massively Unified Code-Key (MUCK) table.

Which brings me to the conclusion that there’s no silver bullet / one size fits all answer here since the need for a “status” column is driven by the needs of the application.