Sergey Sergey - 5 months ago 16
SQL Question

Database representation of a timeline

Say I have a timeline which displays 2 kinds of events:


  1. A patient has visited a clinic. Such an event references to the patient and the clinic.

  2. A patient has uploaded a photo. Such an event references to the patient and the photo.



So I have a few underlying tables here:
patients
,
clinics
,
photos
.

I also have a few timeline tables:
timeline_visit
,
timeline_photo
.

The question is am I forced to use separate tables for every timeline event type and then use
UNION
to build a complete timeline or there are some other techniques that I don't know about (to store all the possible events in one table)?

The tables
timeline_visit
and
timeline_photo
have common and differing fields and keys as you can see.

Answer

I would create a table called events which contains four columns:

events

patient_id int
id int
event_date timestamp
event_type int

Each entry would represent one patient event at one point in time. It could be a clinic visit or an upload of a photo. There would be two tables for visits and photos:

visits

id int
event_id int
doctor_id int
decimal (10, 2) cost
double length_of_visit

uploads

id int
event_id int
image_id int
image_content blob

Now when you want to generate a timeline of events you can accomplish it by querying a single table:

SELECT *
FROM events
WHERE event_date BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 3 HOUR)

The above query would give you a timeline for the last 3 hours. If you want details about each event in the query, then you might have to do a clever UNION query, because the columns will not be the same for each event.

Comments