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.
- 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.
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?
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.
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 🙂