Design : multiple visits per patient

Question

Above is my schema. What you can't see in tblPatientVisits is the foreign key from tblPatient, which is patientid.

tblPatient contains a distinct copies of each patient in the dataset as well as their gender. tblPatientVists contains their demographic information, where they lived at time of admission and which hospital they went to. I chose to put that information into a separate table because it changes throughout the data (a person can move from one visit to the next and go to a different hospital).

I don't get any strange numbers with my queries until I add tblPatientVisits. There are just under one millions claims in tblClaims, but when I add tblPatientVisits so I can check out where that person was from, it returns over million. I thinkthis is due to the fact that in tblPatientVisits the same patientID shows up more than once (due to the fact that they had different admission/dischargedates).

For the life of me I can't see where this is incorrect design, nor do I know how to rectify it beyond doing one query with count(tblPatientVisits.PatientID=1 and then union with count(tblPatientVisits.patientid)>1.

Any insight into this type of design, or how I might more elegantly find a way to get the claimType from tblClaims to give me the correct number of rows with I associate a claim ID with a patientID?

EDIT: The biggest problem I'm having is the fact that if I include the admissionDate,dischargeDate or the patientStatein the tblPatient table I can't use the patientID as a primary key.

It should be noted that tblClaims are NOT necessarily related to tblPatientVisits.admissionDate, tblPatientVisits.dischargeDate.

EDIT: sample queries to show that when tblPatientVisits is added, more rows are returned than claims

SELECT     tblclaims.id, tblClaims.claimType
FROM         tblClaims INNER JOIN
                      tblPatientClaims ON tblClaims.id = tblPatientClaims.id INNER JOIN
                      tblPatient ON tblPatientClaims.patientid = tblPatient.patientID INNER JOIN
                      tblPatientVisits ON tblPatient.patientID = tblPatientVisits.patientID

more than one million query rows returned

SELECT     tblClaims.id, tblPatient.patientID
FROM         tblClaims INNER JOIN
                      tblPatientClaims ON tblClaims.id = tblPatientClaims.id INNER JOIN
                      tblPatient ON tblPatientClaims.patientid = tblPatient.patientID

less than one million query rows returned

Answer 1

I think this is crying for a better design. I really think that a visit should be associated with a claim, and that a claim can only be associated with a single patient, so I think the design should be (and eliminating the needless tbl prefix, which is just clutter):

CREATE TABLE dbo.Patients
(
  PatientID INT PRIMARY KEY
  -- , ... other columns ...
);

CREATE TABLE dbo.Claims
(
  ClaimID INT PRIMARY KEY,
  PatientID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.Patients(PatientID)
  -- , ... other columns ...
);

CREATE TABLE dbo.PatientVisits
(
  PatientID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.Patients(PatientID),
  ClaimID INT NULL FOREIGN KEY 
    REFERENCES dbo.Claims(ClaimID),
  VisitDate DATE
  , -- ... other columns ...
  , PRIMARY KEY (PatientID, ClaimID, VisitDate) -- not convinced on this one
);

There is some redundant information here, but it's not clear from your model whether a patient can have a visit that is not associated with a specific claim, or even whether you know that a visit belongs to a specific claim (this seems like crucial information given the type of query you're after).

In any case, given your current model, one query you might try is:

SELECT c.id, c.claimType
FROM dbo.tblClaims AS c
INNER JOIN dbo.tblPatientClaims AS pc
ON c.id = pc.id
INNER JOIN dbo.tblPatient AS p
ON pc.patientid = p.patientID
-- where exists tells SQL server you don't care how many 
-- visits took place, as long as there was at least one:
WHERE EXISTS (SELECT 1 FROM dbo.tblPatientVisits AS pv
  WHERE pv.patientID = p.patientID);

This will still return one row for every patient / claim combination, but it should only return one row per patient / visit combination. Again, it really feels like the design isn't right here. You should also get in the habit of using table aliases - they make your query much easier to read, especially if you insist on the messy tbl prefix. You should also always use the dbo (or whatever schema you use) prefix when creating and referencing objects.

Answer 2

I'm not sure I understand the concept of a claim but I suspect you want to remove the link table between claims and patient and instead make the association between patient visit and a claim.

Would that work out better for you?