0

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?

3
  • You're not providing much to go on here - do you just want to log edits or do you need to version things because there are different valid values as of a point in time?
    – user212533
    Aug 13, 2021 at 3:16
  • Not sure why it is not clear. We want to provide a complete history of a client. Whatever we capture related to a client. Aug 13, 2021 at 3:26
  • Look into Change Data Capture (CDC) - there are F/LOSS and commercial tools for this!
    – Vérace
    Aug 13, 2021 at 5:42

1 Answer 1

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.

4
  • You can ignore SubType for now. Thats for internal reference. But, How can we have pagination with this query. Say, I want to show only 10 records in a page and 2 records might be from Billing, 2 from Estimate, ... based on date order. Aug 13, 2021 at 3:44
  • That's a different question and you can look up how to do that in MySQL. You're not going to be dealing with a lot of data here, it's not a big deal to send even a few hundred rows with modern hardware, so I'd avoid doing anything fancy on the database end unless absolutely necessary.
    – user212533
    Aug 13, 2021 at 3:48
  • We have lots of data for each client as we maintain last 5+ years of data. Also, as we run in AWS, we will be charged by the data we send. So trying to limit data. Aug 13, 2021 at 3:55
  • It's not that much data and the differential in cost is probably less than how much time it would take to hold one meeting discussing it. Filter on date range and leave it be.
    – user212533
    Aug 13, 2021 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.