Need Help: Tracking changes and comparing records in a table question

Hello,

I am designing a medical system application, where I need to track changes.
for example when an allergy is activated and when it is deactived...

so, I was planning to have a master table, which keep track of who and other business process info, and an allergy entity which has effective date and closed date.

So every time somebody changes the state of an allergy, I create a new record in the allergy entity.

At any give time, I need to find the list of active allergies, which I can get using a stored procedure.

Is this design right? Only thing concerns me is the number of records over time and the performance of getting active records

Has anybody come across such as requirement? I belive it has been a basic requirement of all medical systems.

Thanks
Naveen Received on Wed Jul 17 2002 - 11:11:43 CDT