By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Need little bit of guidance

P: 14
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
Jan 9 '08 #1
Share this Question
Share on Google+
7 Replies


P: 37
you could probably get better luck if you posted specific questions. or even actual segments of code.
Jan 9 '08 #2

mshmyob
Expert 100+
P: 903
Show us the schema and I'm sure we can help you.
Jan 9 '08 #3

P: 14
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.
Jan 10 '08 #4

mshmyob
Expert 100+
P: 903
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.
Jan 10 '08 #5

P: 14
I found how to attach. will be attaching by evening.
Jan 10 '08 #6

P: 14
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.
Attached Files
File Type: zip datam.zip (104.5 KB, 40 views)
Jan 11 '08 #7

mshmyob
Expert 100+
P: 903
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.
Jan 14 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.