0

My question is related to Database Design for Revisions? but more complicated.

I have two tables with many-to-many relations between them. Let's call them A and B.

A: A.id, A.data, A.last_change_time
B: B.id, B.data
A_B: A_B.A_id, A_B.B_id, A_B.connected_since_time, A_B.some_status_enum

The A_B connector table must support revisions. If connection between element in A to element in B is removed historical information that it existed before must be kept. If connection status changes historic info on old status must be kept.

Some of the reports that I need to generate are:

  1. List of all elements in A that are not connected to B at all (now).
  2. List of all elements in A that are connected to at at least one element in B with certain status.
  3. List of all A-B pairs that were once connected but no longer connected.
  4. List of all A-B connections where A's last change time is after the connection time.
  5. Count of all A-B connections where A's last change time is after the connection time grouped by status.

I considered simply adding A_B.is_current boolean field to the connecting table. When connection is deleted I simply set is_current to false. When status changes I set is_current to false for old records and add a new record with new status.

Answers to similar previously asked questions often claim that "is_current" is bad design and there should be a better solution. Previous solutions talked about revision of record in single table and not relations between them.

Is it wrong to use is_current column to track the history of many-to-many connections? If it is wrong what problems it may cause? What is the better solution?

Community
  • 1
  • 1
Muxecoid
  • 1,193
  • 1
  • 9
  • 22

2 Answers2

1

Typically...

  • have 2 extra columns: ValidFromDateTime and ValidToDateTime
  • primary key is then A_id, B_id, ValidFromDateTime
  • ValidToDateTime can be NULL or a sentinel value (eg 99991231)

When a new revision happens, you populate ValidToDateTime of the "current row" and add a new row with the same datetime value for ValidFromDateTime. Or just set ValidToDateTime if this is your business rule.

This gives you state etc (or lack of) for any point in time

Your queries are then quite simple

  1. NOT EXISTS on A_ID
  2. EXISTS on A_ID
  3. EXISTS on A_ID with ValidToDateTime NOT NULL (no current row)
  4. EXISTS on A_ID also comparing ValidToDateTime and last_change_time
  5. LEFT JOIN (same conditions as 4) with aggregate
gbn
  • 422,506
  • 82
  • 585
  • 676
  • This sound logical. Record with null ValidTo is the current record and when I change the relation status I simply update old record with current time. – Muxecoid Aug 22 '11 at 16:08
0

The main issue I see with using an is_current flag is that it's impossible to tell which records are the most recent once they become not current. I would typically add a date field that denotes the start date of when the it is current. Then your query would normally just grab the row with the latest date.

Nick Heidke
  • 2,787
  • 2
  • 34
  • 58
  • I already have connected_since_time field. The problem is that in some reports I need to output the list of all connected pairs in this case is_current feels like a more logical approach than sorting by time and grabbing the maximum. Another problem with having only the revision time is deletion of connections. – Muxecoid Aug 22 '11 at 16:00
  • With the deletion in mind, gbn's response introducing the ValidTo field should solve the issue. With just an is_current field, it would be impossible to tell what was valid at a specific point in time. – Nick Heidke Aug 22 '11 at 16:05