Re: 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.

"Naveen" <tummala_at_kingwoodcable.com> wrote in message news:1ae79ca1.0207170811.e609c88_at_posting.google.com...
> 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 Thu Jul 18 2002 - 19:32:51 CEST