Need Help: Tracking changes and comparing records in a table question 
Author Message
 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



Mon, 03 Jan 2005 00:11:43 GMT
 Need Help: Tracking changes and comparing records in a table question

You're close.

This is a common situation, known as a many-to-many. A person can have one
or more allergies, and an allergy can be had by one or more people.
Furthermore, a person's allergies may start and stop many times. You need
three tables: PERSON, ALLERGY, and PERSON_HAS_ALLERGY

PERSON
person_id PK
name
etc

ALLERGY
allergy_id PK
name
etc

PERSON_HAS_ALLERGY ("linking" table)
person_id PK
allergy_id PK
start_date PK
end_date
etc (but remember not to violate 3NF in this table)

Adding the dates to the linking table allows you to keep a date history for
each person/allergy combination as well, so long as the PKs are as listed.


Quote:
> 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



Tue, 04 Jan 2005 01:32:51 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Track changes to a record in another table

2. HELP:Tracking of changes in database-records

3. Tracking Changes to Records

4. JCN - KEEPING TRACKS OF ALL CHANGES TO RECORDS IN AN ACCESS DATABASE

5. Need help comparing number in two tables

6. newbie: I need to change a field value in all records in a table

7. I need help getting first record from a table, into another table, in a stored proc

8. Question about change Tracking Population

9. Questions about Change-Tracking

10. Tracking Table Schema Changes

11. Tracking Changes to Tables

12. Tracking Table Schema Changes


 
Powered by phpBB® Forum Software