Should I drop this column or is there a query that can achieve a results table without duplicate columns?

Question

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information.

How can I prevent this column, named active, from showing up in the result tables from queries joining a number of tables which contain the active column?

The result table would normally be a summary based on a link table which may have its own active column as well (example: perhaps a schedule table)

This schedule table may contain the names/ids of employees/admins gathered from their own respective tables each of which house an active column.

The goal here is to get educated on queries and keep the active column.

Or just get educated and drop the column from all my tables if it happens to be a silly means of retaining information - whichever works.

Thanks to all that read and respond.

Answer 1

First and foremost only ever name the columns you actually need in the select list. Never ever use SELECT *.

That said, you may already be in a hole out of which you cannot dig. You could create a view to return only the active rows and "data" columns:

create table T(c1 int, c2 int, active bit);

create view V as
select c1, c2 from T
where active = true;

If you rename the existing, underlying table and use the table's current name for the new view you shouldn't have to change any SQL in the application (except for references to the active column, of course).

Whether you should have the active column at all depends on the application's needs and your operational constraints. If you don't need this history for audit/ reporting/ whatever then get rid of the column and DELETE rows when they are no longer useful. Smaller databases are better databases!

If you must retain old records moving them to an archive table is a good solution. It keeps the operational table small and access fast (did I mention that small is good?). You may have foreign key constraints in place; that will complicate matters. A DELETE from the operational table and an INSERT to the archive table will be more work than a simple UPDATE to the active column and hence slower. If this is a problem, marking them inactive, then moving a short time later in a batch process could work.