Ask the Expert

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?


    Requires Free Membership to View

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 editor@searchDatabase.com 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.

This was first published in May 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: