MySQL table design for history table

Question

My requirement is to show the complete history of a client in the History page.

We have following tables to maintain client data.

1. Estimate (Id, Clinet_Id, Name, Total, Date). -- Estimate Data
2. Estimate_Items ( Id, Estimate_Id, ......) -- Estimate Data
3. Billing (Id, Clinet_Id, Name, Total, Date) -- Billing Data
4. Billing_Items (Id, Billing_Id, ....) -- Billing Data
5. Reminder (Id, Clinet_Id, Name, Type, Date) -- Reminder Data
6. Visit (Id, Client_Id, Reason) -- Office visit Data
7. Test (Id, Client_Id, PackageName, Price, Date) -- Test Data
8. Test_Items (Id, Test_Id, Name, ...)

We might add more tables to maintain different data in the future.

Lets say,

Client history is as follows

1. Day 1 - We send Estimate to a client
2. Day 2 - Client comes to office and goes for a Test & pay Billing for the test
3. Day 3 - We send Reminder

So, the history page should be having the above details sorted by date. The history will be shown at a higher level and it can be expanded to view the details.

My Data Model for History.

Table:- History(Id, Name, Type, SubType, Date)

Sample data in History,

1, 'Test Estimate', 'Estimate', 'Test', '01-01-2021'
2, 'Wellness Test', 'Test', 'Wellness', '01-02-2021'
3, 'Test Billing', 'Billing', 'Test', '01-02-2021'

So, the history page will have the data from here ordered by date and on clicking expand for each item, We will fetch the details from the corresponding table based on Type and show the details.

Is there any better data model in for MySQL?

Answer 1

You can get everything you need from the tables you have. You didn't provide detail for how to determine Subtype, but you can place that logic within the sub-select.

SELECT
  Type
 ,Name
 ,Date
FROM
  (
    SELECT
      Client_Id
     ,'Estimate' AS Type
     ,Name
     ,Date
    FROM
      Estimate
    
      UNION ALL

    SELECT
      Client_Id
     ,'Billing'
     ,Name
     ,Date
    FROM
      Billing

      UNION ALL

    SELECT
      Client_Id
     ,'Test'
     ,PackageName
     ,Date
    FROM
      Test

      UNION ALL

    SELECT
      Client_Id
     ,'Reminder'
     ,Name
     ,Date
    FROM
      Reminder
  ) ClientSummary
WHERE
  Client_Id = 1
ORDER BY
  Date
 ,Type
 ,Name

If MySQL isn't smart enough to push the predicate Client_Id = 1 inside the sub select you'll need WHERE Client_Id = 1 on every select on the inner query.