Database design review - tracking of many statuses

Question

I have designed the following database for a small project. Please review and leave feedback. The main problem I'm having is to keep tracking of many statuses. The user wants to know the status of particular action on a given date. Do I have to create seperate table for just statuses with date added, date modified etc?

Answer 1

Considering our future requirements I would like to have a good domain model.

But if you observe the current design I'm not able to keep track of dates and history.

Would you mind pointing to specific entity how I can improve it ? Like create a separate table for statuses ?

I just realized I posted with different account, now its grabbing the account from Stackoverflow. Sorry for confusion.

Answer 2

If you break out the status table you could add an table that defines how states transition from one state to the next.

This data would be used to encode rules like "you can't have a FinalConsentStatus without a PtApproachStatusYes". Without this you must encode each rule in your source code and any change to the rules require a redeployment. There are however new problems like what happens to changes to transitions that are "in flight"

                                                        TransitionHistory
                                 Transition             +--------------+
                                 +------------+-------->|ID            |
                                 |ID          +         |--------------|
                                 |------------|         |PatientsetID  |
                                 |Name        |         |TransitionID  |
         State    +------------->|FromState   |         +--------------+
         +--------+------------->|ToState     |
         | ID     +              +------------+
         |--------|
         | Name   |
         +--------+

Answer 3

I would recommend separating the patient transactions from the patient profile, I think this is the status, into separate table.

Depending on the business, you want to look at it as a transaction, which has different updates, including letter sending, phone followups etc, multiple status values from which you can get a current status along with a history of what was done.

Answer 4

1. No need to end the word with Set. Everyone knows it's a set.

2. Using singular names for tables makes more sense when you are using an ORM (Doctor doctor = new Doctor())

3. Doctors, patients, and Hospital Organizations are all legal persons (Party Model).

4. Doctors can be patients and vice versa

   Party -< Roles {doctor, patient}

5. An Address is its own type. A hospital has 1+ addresses (or 1 address with many buildings). A doctor has 1+ addresses (home, office, cottage). A patient has 1+ addresses. These addressPlacements can change over time too.

   Party -< PartyAddress >- Address

6. A patient has many symptoms, visits, diagnoses, treatments, etc.

7. To track a status over time i would go with something like this:

   SomePatientStatus
   patientId int references Patient
   somePatientStatusTypeId int references SomePatientStatusType
   created datetime

Answer 5

How I would lay things out is below. This will normalize the information being saves. There is only one table for information about people, no matter if a patient or a doctor. There is only one table to hold addresses, no matter for patient, doctor, or hospital.

The layout allows for any number of statuses, and keeps a running history in the PatientStatus table - just never update or remove, only add new entries. New status types can be added at any point by adding a new entry in the Statuses table - any number no limit.

People
------
Id
Title
FirstName
MiddleName
LastName
OtherName
Gender
DateOfBirth


Addresses
---------
Id
Type
Line1
Line2
City
State
Zip
Country


PersonAddresses
---------------
Id
PersonId
AddressId


Phones
------
Id
Type
Number
Ext


PersonPhones
------------
Id
PersonId
PhoneId


Emails
------
Id
Type
Address


PersonEmails
------------
Id
PersonId
EmailIs


Hospitals
---------
Id
Name


HospitalAddresses
-----------------
Id
HospitalId
AddressId


Doctors
-------
Id
PersonId
HospitalId


Patients
--------
Id
PersonId
DoctorId


Statuses
--------
Id
Name


PatientStatuses
---------------
PatientId
StatusId
EntryDate


Letters
-------
Id
Name


PatientLetters
--------------
Id
PatientId
LetterId
EntryDate
ResponseDate
ResponseReceived

Some example queries:

List All Doctors
----------------
SELECT P.* FROM Doctors AS D INNER JOIN People AS P ON D.PersonId = P.Id


List All Doctors at a given Hospital
------------------------------------
SELECT P.* FROM Doctors AS D INNER JOIN People AS P ON D.PersonId = P.Id
                             INNER JOIN Hospitals AS H on D.HospitalId = H.Id
WHERE H.Name='ABC Hospital Inc.'


List Current Patient Statuses
-----------------------------
SELECT P.*, S.Name, PatS.EntryDate FROM Patients AS Pat
                              INNER JOIN People AS P ON Pat.PersonId.Id = P.Id
                              INNER JOIN PatientStatuses AS PatS ON PatS.PatientId = Pat.Id
                              INNER JOIN Statuses AS S ON PatS.StatusId = S.Id
ORDER BY PatS.EntryDate DESC

Answer 6

If you ever want to add more statuses, you'll probably want to have a flexible structure. If you're 100% sure you'll never need more statuses, a denormalized design is just as good as a normalized one.

Although, if you want to have a nice domain model with current state and history etc - possibly using an ORM with inheritance and behavior for states - you have to go with a normalized structure.