MySQL table design for history table

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply