What is the best Table Structure?

[Yak77]

Hi All,

I am trying to help a friend create a database for a rehab hospital. When Patients are admitted they run a series of exams on them and when they are discharged they run the same exams. The same Patient could come back multiple times, so each time they come back ("Episode of Care") they would get an Admission exam and Discharge exam. Eventually, they would like to be able to run reports where they could compare discharge exam scores to the corresponding admission exam scores filtering on things like Patient age, gender, etc.

I have come up with a couple of different possibilities of how to set up the data and the relationships and have included them in the attached zip file. I just put in a sample data structure to try and convey the different options, the final database will have many more exams and data points.

In Db1, I have an EpisodesOfCare table that has an AdmissionExamID and DischargeExamID as foreign keys into an Exams table (one to one relationship). In Db2, I removed the AdmissionExamID and DischargeExamID from the EpisodesOfCare table and instead put an EpisodeOfCareID and ExamTypeID (Admission or Discharge) into the Exams table.

Is there another (better) way to organize this data? What is the preferred option and why? How does one decide between the different options available?

Note, I also broke up the exams table into subexams, because there is going to be a fair bit of data for each of these subexams (when they do the exam, they do break it down into different types of subexams). So even though there is a one to one relationship between the exams table and the subexams, it seemed like putting all that data into one main exams table would be too much and harder to keep track of in the future. Is that a reasonable way of breaking things up? In Db1 I had SubExamIDs in each subexam table, but in Db2 I realized I could just use the ExamID as the primary key in each subexam. Is that a better way to relate the tables? Any drawbacks to that?


Thank you for any advice and help you can provide.

 

[jdraw]

Suggest you put a few more attributes in the tables to help us understand.
I don't follow the SubTypeExam approach -- examples please.

Isn't an Episode of Care something like a few queries or a report?


This link may help with an approach
http://www.databaseanswers.org/approach2db_design.htm

 

[MarkK]

Two rules:
1) Each record must represent a single discrete thing, so you can represent at most, one datapoint per record.
2) All objects having the same data structure should be in the same table, and where they differ by type they should be distinguished by the value of field.

In violation of 1) your table(s) that contains numbered data points 1, 2, and 3 are liabilities. Each of those fields should be a single record.
In violation of 2) your tables named subtype1 and subtype2. Those should be a single table with the subtype designator being a field in the table.

Typically if you need to name something with a number at the end, like somefield1 and somefield2, or sometable1 and sometable2, then you probably have design problem.

 

[Yak77]

Thank you for the replies. I updated the Original Post with a hopefully more clear example of what the database will look like.

In my attempt to try to not confuse people with the actual names of the exams, I probably managed to do the exact opposite.

Basically, an Episode of Care is essentially a stay at the hospital. When a person is Admitted an exam will be run. The exam consists of tests that have scores or times that can be used as a baseline. Associated tests are grouped together such as those dealing with "Pitch Range", "Sustained Ah", etc. When the person is discharged, those exact same tests will be run again. One thing to note is the same person can come back multiple times, thereby having multiple "Episodes Of Care" and multiple sets of Admission - Discharge tests done.

Eventually, they would like to be able to run reports to compare the scores from Admission to Discharge.

I hope that clears up my original questions. Thanks again for any further guidance you can provide.