Problem solve Get help with specific problems with your technologies, process and projects.

Active and inactive records

I am trying to have Vendor Master table which has Secondary tables linked via Primary Key. I now want to have a Vendor removed from the Vendor Master based on business logic, with all related records from Secondary tables, to a Vendor_NonActive_Table and Secondary records to their respective tables.

I also want the ability to allow for Non-Active Vendors to be moved back to Active Master and made Active but want to link past records in the Non-Active Master and new records in the Active Masters.

How would I do this, and will Oracle 8i allow this capability?

There are two options -- physically move the records to a new table, or flag them as inactive and leave them in the table along with active records. I prefer the latter strategy. Moving records (INSERT into a target table, DELETE from source table) is expensive in terms of database cycles, compared to an UPDATE with SET ACTIVE='N'.

I'm not exactly sure what you mean by "link past records in the Non Active Master and new records in the Active Masters" but it suggests that you might possibly have to do a join from an active master to both an active and an inactive secondary table (thus, probably a UNION). This complexity can be avoided if there are no inactive tables.

Oracle is an industrial strength database. You will have no noticeable deterioration in performance by having the inactive records in the same tables as the active ones (unless your tables have gazillions of rows). Make sure you declare indexes which include the "active" column. You can even declare views which pre-select only active records.

For More Information

  • What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL