newbie needs help

[hollyh]

t

so I am working on a database which was set up wrong. currently they have a floating table with zero relationships called diagnosis. they use this table in a query, to get the TEXT of the primary dx and secondary dx in a VIST table.

so there's 3 tables, but really only two being used.

patient
patiendID

visit
visitID (pk)
patientID (fk)
primaryDx (query to below)
secondaryDx (query to below)

diagnosis
dxID (not being used)
description

to correct this should i make a new table like
vistPrimaryDX
vistPrimaryID
visitID
dxID

and
VisitSecondaryDX
VisitSecondaryID
visitID
dxID

or am i going about this all wrong? thanks

 

[Beetle]

What is the nature of the Diagnoses themselves whithin the context of your business model. In other words, which one of the following is true;

1) There is always a Primary and a Secondary diagnosis.

2) There is always a Primary and sometimes a Secondary.

3) There could be one or the other or both.

Also, can there ever be more than just the Primary and Secondary (like a third or fourth diagnosis)?

 

[hollyh]

Hello, sorry I did not see your reply!

none of these fields are mandatory
so there are mostly primary dx's but not all the time
and half the time there are secondar dx's
no third dx