1

I have a query on database table structure. My Patient Details table in a SQL Server 2008 database looks like this:

--PatientId(PK)-- --PatientType-- --DoctorId(FK)-- --DateOfVisit-- --PrescriptionId(FK)--

Each patient may visit the hospital multiple times. I need to link all the visits of a patient and show them in a linked way that the admin can navigate with the previous and next visits..

So I think I can do it only by having a record for each visit with a VisitId.

I need to be Careful that the database should not be loaded with unnecessary fields. Also it should not affect the fetching time badly.

I think these are the available ways. Suggest me if any available.

--PatientId(PK)-- --PatientType-- --DoctorId(FK)-- --DateOfVisit-- --PrescriptionId(FK)-- --VisitId-- --PrevVisitId-- NextVisitId--

or

having a separate table for Visit as

--VisitId(PK)-- -PrevVisitId(FK)-- --NextVisitId(FK)--

Apologies if my post is duplicate.. Correct me, redirect me wherever/whenever needed.

Lakshminarayanan Guptha
  • 231
  • 1
  • 13

1 Answers1

1

So I think I can do it only by having a record for each visit with a VisitId.

You need a row for each visit. You need a key for each row. The key doesn't need to be a single id number. I think the very least you need to record for each visit is

create table visits (
  patient_id integer not null, -- references patients (patient_id), not shown
  doctor_id integer not null,  -- references doctors (doctor_id), not shown
  office_visit_start timestamp not null default current_timestamp,
  primary key (patient_id, office_visit_start)
);

insert into visits values (1, 1, '2012-02-01 08:00');
insert into visits values (1, 1, '2012-02-01 15:00');
insert into visits values (1, 1, '2012-03-01 09:33');
insert into visits values (2, 1, '2012-02-01 09:00');

(Exact syntax varies depending on your dbms platform.) You can find the previous and next visits by fairly simple queries--"office_visit_start" will give you the order of a patient's visits.

Example queries . . .

-- Previous visit for patient # 1 (before 2012-02-01 15:00)
select patient_id, max(office_visit_start)
from visits 
where patient_id = 1
  and office_visit_start < '2012-02-01 15:00'
group by patient_id

-- Next visit for patient # 1 (after 2012-02-01 15:00)
select patient_id, min(office_visit_start)
from visits 
where patient_id = 1
  and office_visit_start > '2012-02-01 15:00'
group by patient_id
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks. You greatly helped me. But Sorry I couldn't get this `primary key (patient_id, office_visit_start)` Can we have two primary keys in a same table? – Lakshminarayanan Guptha Aug 18 '12 at 06:18
  • One primary key, consisting of two columns. I'm sure your database can manage that; your framework might not, though. – Mike Sherrill 'Cat Recall' Aug 18 '12 at 11:26
  • Another query please.. What if I have my Patient Details table as `--PatientId-- --PatientType-- --DoctorId(FK)-- --Office_Visit_Start-- --PrescriptionId(FK)--` with (PatientId,Office_Visit_Start) as primarykey.? By this I can avoid creating a new Visit Table. How efficient will this be, than for creating a new visit table? Thanks. – Lakshminarayanan Guptha Aug 20 '12 at 05:44
  • I don't think that's a good structure in general. It would record PatientType for every visit, which seems odd to me. (But *could* be correct--it depends on what "patient type" means.) It also limits you to one prescription per office visit, which I think isn't realistic. – Mike Sherrill 'Cat Recall' Aug 20 '12 at 11:36
  • PatientType(In/OutPatient). One prescription/vist/doctor in the sense if a patient visits 3 doctors of same hospital, same day with diff symptoms, he might be prescribed diff medicines. So I think I should have one row/visit each with a prescriptionid, doctorid. Or there should be some other way of approach. – Lakshminarayanan Guptha Aug 20 '12 at 16:13
  • In-patient/Out-patient makes good sense here. But it's not unusual for a single visit to a doctor to result in two or three prescriptions. For example, on a single office visit involving a single doctor, a diabetic might get prescriptions for 1) insulin, 2) lancets, 3) a statin, and 4) an antibiotic. If you were trying to say you need a table for prescriptions, you're right. – Mike Sherrill 'Cat Recall' Aug 20 '12 at 16:18
  • I'm afraid! Having officevisit(timestamp) as primarykey, won't cause issues when my app is going to be installed abroad that varies from our time zone? (FYI: time we use runs at our centralized server) – Lakshminarayanan Guptha Aug 24 '12 at 09:09
  • I suggested using the pair of columns {patient_id, office_visit_start} as the primary key. That seems to make sense, because a) an individual patient can't start two different office visits at the same, and b) an individual *might* have two office visits in a single day. Time zone shouldn't raise any problems with what you asked here, but it might raise problems in other ways. – Mike Sherrill 'Cat Recall' Aug 24 '12 at 11:21
  • I already understood the primary key & its case study. Sorry for my prev comment isn't clear. `but it might raise problems in other ways` What might be other problems? & Im concerned about time zone and dst that varies among diff countries. In cases like "if an entry is being made at U.S, while the visit time is fetched from the one and only server that we have @ India, we should convert time zone each time a row is recorded, provided we were informed about their exact location, dst" I guess its problematic. Is there anyother way to avoid these complexities? Or is there any predefined scripts? – Lakshminarayanan Guptha Aug 25 '12 at 07:30
  • 1
    Always store UTC. See [this](http://stackoverflow.com/q/3207976/562459) StackOverflow question. – Mike Sherrill 'Cat Recall' Aug 25 '12 at 11:40