0

I'm looking for a database model to efficiently query the temporal relationship between different events. Where an "event" is an entity with a start and an end date.

Simplified example:

Find all medications that happened during an operation which happened during an admission.

Admission, operation and medications are all "events" but they also have their own specific columns, so traditionally in an RDBMS they are stored in their own tables.

For this example let's say admission, operation and medications have ~10^7 events each.

For an example of how one might try to solve a similar problem in postgres see this question/answer: How to optimize query to compute row-dependent datetime relationships?

shusson
  • 5,542
  • 34
  • 38
  • Please share more details about your attempts to resolve this problem – Nico Haase Apr 13 '21 at 10:11
  • @NicoHaase I added a link to a similar question that is specific to postgres. But I'm keen to hear solutions at a higher level. One area I recently looked into were temporal databases, but they seem to focus on the history of one table. – shusson Apr 13 '21 at 10:17
  • I think that you may prefer to go for the least match first. Example: I would select first the admission, use it to filter the operations and then collect all the medications. Since we are talking about transactional SQL, all queries that have exactly one match should be executed really fast with respect to the gathering ones. – Marco Massetti Apr 19 '21 at 21:34
  • restricting the search to the top event is essentially what we do now, but I am curious if there are databases or data models that let you query for all combinations efficiently. – shusson Apr 20 '21 at 09:28

0 Answers0