Please verify this is normalized and correc structure...

[David Benyo via AccessMonster.com]

I think I've stretched myself thin lately and this just isn't making sense to
me at this moment. It's a pretty basic database that'll be used to track each
patient visit from when the patient is seen to when the finalized (sent to
the billing company). If you have any other suggestions for this structure,
please let me know. If it looks good, I'll most likely have more questions as
I am struggling with a couple things related to forms and reports. Here's the
table structures and relationships:

tblPatients
MRNumber (PK)
FirstName
LastName
Address
City
etc...

tblEDLog
AccountNumber (PK)
MRNumber (FK to tblPatients)
DateofService
EDMD
EDPA
PresentingComplaint
ArrivalTime
etc....

tblProviders
ProviderID (PK)
ProviderFirstName
ProviderLastName

tblProcesses
ProcessName (PK)
ProcessDescription

tblProcessTracking
ProcessID (PK - Autonumber)
AccountNumber (FK to tblEDLog)
DateofProcess
Process (FK to tblProcesses)
ProviderResponsible (FK to tblProviders)
ProcessNote

 

[Guest]

I'd be worride about the EDMD and EDPA fields. What if they are seen by more
than one doctor or phyicsian's assistant or are not seen by both a EDMD or
EDPA.

You might want another table filled with ED personnel something like:

ED_ID ED_NAME ED_Position
1 Howard MD
2 Fine MD
3 Howard PA
4 Rachet RN

That way you could track multiple people who see the patient.

 

[David Benyo via AccessMonster.com]

Sorry, I probably should've mentioned this before. tblAllPatients, tblEDLog,
tblProviders are generated by the hospital. In addition, the hospital
"assigns" one and only one EDMD and/or EDPA to each visit...although you are
correct in thinking more than one provider can see the patient. Unfortunately,
that's not how it's tracked at the hospital level and therefore cannot be
tracked on my database level either.

That's why I'm splitting out to tblProcessTracking and assigning the
responsible provider. Although the provider listed in tblEDLog is only one,
the person verifying each visit's file is complete can assign responsibility
to the provider responsible.

I'd be worride about the EDMD and EDPA fields. What if they are seen by more
than one doctor or phyicsian's assistant or are not seen by both a EDMD or
EDPA.

You might want another table filled with ED personnel something like:

ED_ID ED_NAME ED_Position
1 Howard MD
2 Fine MD
3 Howard PA
4 Rachet RN

That way you could track multiple people who see the patient.

I think I've stretched myself thin lately and this just isn't making sense to
me at this moment. It's a pretty basic database that'll be used to track each

[quoted text clipped - 38 lines]

ProviderResponsible (FK to tblProviders)
ProcessNote