`active' flag or not?

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


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.