Re: Database History

From: Pablo Sanchez <pablo_at_dev.null>
Date: Tue, 11 Mar 2003 23:16:52 -0600
Message-ID: <Xns933BE2A8BF2ECpingottpingottbah@216.166.71.233>

lieut_data_at_yahoo.com (lieut_data) wrote in news:527869d5.0303111612.50027d77_at_posting.google.com:

> Or should I not delete users/hardware at all? Just mark them
> "inactive", preventing them from showing up in the main list, where
> they are not used. But what if we want to use a username again, for
> a new employee?

I'm currently working on a system that has the exact need that you mention. What I've done to solve this is to add an 'is_active' flag to the rows that need to be preserved.

For rows that can potentially be reused, you'll need to implement some logic in your stored procedures.

For example, the username issue that you mention above, say your user table was something like the following:

user_info
  id NUMBER(10),

  user_name   VARCHAR2(20),
  first_name  VARCHAR2(20),
  last_name   VARCHAR2(20),

  is_active NUMBER(1) -- 0 - inactive; 1 - active

The stored procedure will need to ensure that at any one time, there can only be one occurrence of 'user_name' + is_active == 1 On the other hand, there can be many occurrences of 'user_name' + is_active == 0

You can also handle the above logic in a trigger.

Thx!

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com

Received on Tue Mar 11 2003 - 23:16:52 CST