`active' flag or not?

Question

OK, so practically every database based application has to deal with "non-active" records. Either, soft-deletions or marking something as "to be ignored". I'm curious as to whether there are any radical alternatives thoughts on an `active' column (or a status column).

For example, if I had a list of people

CREATE TABLE people (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(100),
  active   BOOLEAN,
  ...
);

That means to get a list of active people, you need to use

SELECT * FROM people WHERE active=True;

Does anyone suggest that non active records would be moved off to a separate table and where appropiate a UNION is done to join the two?

Curiosity striking...

EDIT: I should make clear, I'm coming at this from a purist perspective. I can see how data archiving might be necessary for large amounts of data, but that is not where I'm coming from. If you do a SELECT * FROM people it would make sense to me that those entries are in a sense "active"

Thanks

Answer 1

You partition the table on the active flag, so that active records are in one partition, and inactive records are in the other partition. Then you create an active view for each table which automatically has the active filter on it. The database query engine automatically restricts the query to the partition that has the active records in it, which is much faster than even using an index on that flag.

Here is an example of how to create a partitioned table in Oracle. Oracle doesn't have boolean column types, so I've modified your table structure for Oracle purposes.

CREATE TABLE people (    id       NUMBER(10),    name     VARCHAR2(100),    active   NUMBER(1) ) PARTITION BY LIST(active) (    PARTITION active_records VALUES (0)    PARTITION inactive_records VALUES (1) ); 

If you wanted to you could put each partition in different tablespaces. You can also partition your indexes as well.

Incidentally, this seems a repeat of this question, as a newbie I need to ask, what's the procedure on dealing with unintended duplicates?

Edit: As requested in comments, provided an example for creating a partitioned table in Oracle

Answer 2

Well, to ensure that you only draw active records in most situations, you could create views that only contain the active records. That way it's much easier to not leave out the active part.

Answer 3

We use an enum('ACTIVE','INACTIVE','DELETED') in most tables so we actually have a 3-way flag. I find it works well for us in different situations. Your mileage may vary.