Ok I have Normalized your Report and come up with a design. Your Business rules were incomplete so I have made some assumptions and will let you know.
1. I have assumed that each Patient has one Doctor (if they have more than 1 then just add a bridge table).
You will see a Test, Category, and SubCategory table
Test = is like RBS, or Urine Analysis, or Urine Analysis Detail etc. in your report (ie: the left most in column under Test in the report.
Category = is like Polymorphs, Color, etc. (the 2nd indent in the Test column in your report)
SubCategory = is like Pus Cells, RBC, Casts, etc. in the report. (ie: the 3rd indent under column Test in your report).
8 tables
Doctor - Doc_ID, Doc_Name
Patient - Patient_ID, Patient_Name, Doc_ID
Report - Report_ID, Cust_ID, Date
Report Detail - Report_ID, Test_ID, Cat_ID, Sub_ID, Reading
Test - Test_ID, Test_Desc
Category - Cat_ID, Cat_Desc
SubCategory - Sub_ID, Sub_Desc
Normal - Test_ID, Cat_ID, Sub_Desc, Norm_Reading, Price
You can figure out the links by the PK's and FK's
Price for an individual test is stored in the Normal table and can just be summed in code for a test that consists of more than one test to get only a total to show in the report.
I hope I understood your requirements. Using this schema I could generate your reports that you attached. Remember do not create your schema to create your report. The schema is used to reduce data redundancy and along with coding will generate your needs.
Hi me again. I am attaching the zip file. It contains business requirements doc, snap shot of schema, dbm file(MS Access) which contains some original data. Kindly have a glance at my solution and point out the mistakes i have done. It helps me a lot. Thanking you all.
14
