opinion question - permanantly deleting records

I have a database "philosophy" question. What are some opinions on
permanant record deletion? This would apply to personnel tables,
inventory tables, or whatever. From an accountant's point of view
records of important information such as this should not be
permanently deleted (e.g. you have 1,500 widgets of a particular SKU
in inventory, wouldn't you want to know every transaction in and out
of inventory from which this figure were derived?). Another case: You
have a total of 1,000 current and ex-employees who have various merit
and/or discipline actions taken based on performance. A record in the
performance files for all employees is generated each time an action
of significance is taken. For legal reasons I would think these
records should reside in the database perpetually. Some users are
concerned that former employees may show up on reports. My proposed
solution is simply to have a field that indicates whether or not an
employee is active or inactive.
Any thoughts on these issues? We don't have a storage problem that is
presenting this, we have rather hefty server space.
Any "White Papers" anyone could recommend would be appreciated.
Thanks!Slinky
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
I think you are on the right way..
"slinky" <campbellbrian2001@.yahoo.com> wrote in message
news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!|||Thanks! Do you know of any MS or Oracle documentation that addresses
this issue?
On Feb 18, 10:18=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Slinky
> > concerned that former employees may show up on reports. My proposed
> > solution is simply to have a field that indicates whether or not an
> > employee is active or inactive.
> I think =A0you are on the right way..
> "slinky" <campbellbrian2...@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>
> >I have a database "philosophy" question. What are some opinions on
> > permanant record deletion? This would apply to personnel tables,
> > inventory tables, or whatever. From an accountant's point of view
> > records of important information such as this should not be
> > permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> > in inventory, wouldn't you want to know every transaction in and out
> > of inventory from which this figure were derived?). Another case: You
> > have a total of 1,000 current and ex-employees who have various merit
> > and/or discipline actions taken based on performance. A record in the
> > performance files for all employees is generated each time an action
> > of significance is taken. For legal reasons I would think these
> > records should reside in the database perpetually. Some users are
> > concerned that former employees may show up on reports. My proposed
> > solution is simply to have a field that indicates whether or not an
> > employee is active or inactive.
> > Any thoughts on these issues? We don't have a storage problem that is
> > presenting this, we have rather hefty server space.
> > Any "White Papers" anyone could recommend would be appreciated.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||I prefer to model this with pairs of dates to indicate a period. For
example, with employees you can have a table that records employee start and
end date for employment. When employee is hired record the start date and
end date is NULL. When employee leaves the company, update the end date to
record that. And having this in a separate table will give you the option to
record multiple events for employee (for example, if rehired). Based on that
you can easily select current active employees, those are employees that
have end date NULL. This will also provide a good trail for auditing if
needed, and it provides much more information that a simple flag.
If you have only a flag for active/inactive, then you can run into some
weird reporting problems, like if somebody asks to run a report of employees
that were active last year. Then if you have an employee that became
inactive in the beginning of this year, you will exclude the employee from
the report because the flag shows inactive, but this is incorrect because
the employee was active last year. Keeping a period of dates will help you
to answer this question correctly.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Does the philosophy include securing the information?
"slinky" wrote:
> I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!
>|||I think it greatly depends on the type of data and the size.
if you're generating a million+ rows of, say, statistical data a day that
you'll only need a 90 day trending on, you obviously want to archive that
stuff and purge it from the database as a matter of course.
Personnel records, on the other hand, are generally required to be kept by
law for certain periods. I've heard 7 years, but a lot of companies keep
that info in perpetuity, which is not a bad idea. The size of a table
related to personnel does not tend to grow out of control.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SRQZFkcIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Slinky
>> concerned that former employees may show up on reports. My proposed
>> solution is simply to have a field that indicates whether or not an
>> employee is active or inactive.
> I think you are on the right way..
>
>
> "slinky" <campbellbrian2001@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>>I have a database "philosophy" question. What are some opinions on
>> permanant record deletion? This would apply to personnel tables,
>> inventory tables, or whatever. From an accountant's point of view
>> records of important information such as this should not be
>> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
>> in inventory, wouldn't you want to know every transaction in and out
>> of inventory from which this figure were derived?). Another case: You
>> have a total of 1,000 current and ex-employees who have various merit
>> and/or discipline actions taken based on performance. A record in the
>> performance files for all employees is generated each time an action
>> of significance is taken. For legal reasons I would think these
>> records should reside in the database perpetually. Some users are
>> concerned that former employees may show up on reports. My proposed
>> solution is simply to have a field that indicates whether or not an
>> employee is active or inactive.
>> Any thoughts on these issues? We don't have a storage problem that is
>> presenting this, we have rather hefty server space.
>> Any "White Papers" anyone could recommend would be appreciated.
>> Thanks!
>