Business concept design vs logical database design

Question

Good day. I need help for a specific case. A little background: We have an existing app, it is like a PDF viewer, and you can draw freehand, highlight, add highlight with notes, add action items, etc.

In this discussion, I'll discuss the two database tables 1) ACTION_ITEM table containing the list of action items of a document, and 2) HIGHLIGHT table containing the highlight and the notes.

The difference between a highlight note and an action item is that in action item, you can assign it to a person, specify a due date, and mark it as completed.

Currently we added an enhancement, for the end user to convert a highlight note to an action item. So we added a checkbox "[ ] Action Item". When you convert to an action item, the UX of it will still look the same: it visually still looks like a note, it is still associated with a highlight, and it still located on a page, except that it has the "Action Item" checked. (Our "usual" action item, is not associated to a page).

The programmers (including me) already coded the enhancement (this is in multiple platforms), in such a way we knew best: We added the needed new columns to HIGHLIGHT table: due date, assigned person, status and an action item indicator.

Now here's the tricky part, there's an ongoing design discussion to change the physical design to transfer the highlight note marked as an action item to ACTION_ITEM table. This means, copying all the columns needed in the HIGHLIGHT table like highlight rectangle, note location, page number, document id, etc and copying it to ACTION_ITEM table.

This only applies to highlight notes marked as an action item. If the highlight note is not marked as an action item, then we still need to store it in HIGHLIGHT table. Currently, marking a note as an action item, we just change a column, in the new design, we have to transfer the entire record to another table.

Not only that the physical design will change, but all logic related to its storage and retrieval (which is a lot).

The reason is, the Systems Analyst said that instead of programming for convenience, we need to align the physical design to the business concept. In this case, an Action Item is a different business concept so it needs to be in the ACTION_ITEM table.

My question is, is Systems Analyst correct? Do we merge a physically different entities in the same table, just because it's the same business concept? Seems illogical to me as a programmer. It's not merely for convenience, it's for efficiency.

Answer 1

I think the SA is right. (in that you should have actions in the actions table) But for more concrete reasons than just 'align with business definitions'

1: By adding the exta cols to highlight you have broken the normalisation of that table.

2: you presumably do stuff with actions, now you have to check two places to get them all. breaking the single source of truth principle.

Obviously the same reasons would apply to 'copying the highlight fields to action' so your SA is just as wrong if there are suggesting that

Instead I would have added an association between actions and highlights.

ie.

ACTION_ITEM
  Id
  FieldsForAction

HIGHLIGHT 
  Id
  FieldsForHighlight

HighlightsWithActions
  HighlightId
  ActionItemId

so when you tick the highlight to make it an action, you populate a new ActionItem and add a row to HighlightsWithActions specifinging the selected highlight and the Id of the new ActionItem you created.

You can add unique indexes to limit the relationship to a 1-0/1 or 1-many or many-many as required

In the long run this will make the programming easier as the business will ask for new stuff like 'make all the actions flash red on tuesdays' etc