Need little bit of guidance

[yaragallamurali]

Hi I have thought about my earlier post, refined it and reposting it. I am actually new to schema designing. I have read few articles about data modeling and started building schemas for real time data. I have done one. I need guidance in my first steps. Just some one who can have a glance at it and can suggest mistakes that I have done. So that I can correct them and learn. If any one have interest to provide little bit of guidance please respond.

Any guidance at this stage would be a great help to me. Thanking you so much

[cmartin1986]

you could probably get better luck if you posted specific questions. or even actual segments of code.

[mshmyob]

Show us the schema and I'm sure we can help you.

[yaragallamurali]

Kindly tell me how to post the schema here. actually i want to add a zip file which contains the schema. how can i do that? Thanks for your replies.

[mshmyob]

I don't know if you can put attachments.

Just indicate the table names and field names and tell us your primary and foreign keys and we will figure it out.

If you can list a data dictionary.

Kindly tell me how to post the schema here. actually i want to add a zip file which contains the schema. how can i do that? Thanks for your replies.

[yaragallamurali]

I found how to attach. will be attaching by evening.

[yaragallamurali]

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.

[mshmyob]

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.