My requirement is to show the complete history of a client in the History page.
We have following tables to maintain client data.
- Estimate (Id, Clinet_Id, Name, Total, Date). -- Estimate Data
- Estimate_Items ( Id, Estimate_Id, ......) -- Estimate Data
- Billing (Id, Clinet_Id, Name, Total, Date) -- Billing Data
- Billing_Items (Id, Billing_Id, ....) -- Billing Data
- Reminder (Id, Clinet_Id, Name, Type, Date) -- Reminder Data
- Visit (Id, Client_Id, Reason) -- Office visit Data
- Test (Id, Client_Id, PackageName, Price, Date) -- Test Data
- 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
- Day 1 - We send Estimate to a client
- Day 2 - Client comes to office and goes for a Test & pay Billing for the test
- 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?