Help with database design approach

I am trying to get started in database design. But I have run across a project where I am not sure how to approach the design. I have picked up the SQL Server 2000 database design book for some help, but I still need a jump start...

The project involves creating a questionaire to capture information about health care research reports. But there are many levels to the questionaire where specific questions are asked within each level. I hope the outline I included helps (I used example information).

The purpose of the database would be to ask questions like "which studies focused on the cost of plans accepted by doctors" or "which studies looked at the amounts patients paid for coverage".

But as you can see users can selected multple Study Areas, multiple Subject Areas and multiple Focus Areas. So I am kind of at a loss. They are all many-to-many. It also seems if the customer wants to add even more levels it would really get crazy.

I created four tables: researchReport, studyArea, subjectArea, and focusArea. If I only had to worry about researchReport and studyArea I can understand creating a joining table - researchReport_studyArea. But I am not sure how I account for the fact that I have to deal with subjectArea and FocusArea. Do I have to keep creating joining tables? And as I go deeper do I keep the PK of the previous two tables? And will the queries get really complicated when I need to answer questions of the database?

So a little help would be appreciated. I realize I have asked a lot. I appreiate any help to get started!

Brenda

The [] are supposed to be check boxes...

<outline>

Indicate Study Area (select multiple)

[] Doctors

     Subject Area(s) & Focus Area(s)
      [] Plans Accepted


           [] Years Accepted


           [] Cost of Plans



      [] Practice Type


           [] Years in Practice

           [] Geographic Area


           [] Number of Doctors


      

[] Patients

     Subject Area(s) & Focus Area(s)
      [] Family


           [] Number of Children


           [] Years of Coverage



      [] Amount Paid for Coverage
      [] Satisfaction with Coverage
      [] Usage


           [] Frequency


           [] Acceptance


      

[] Health Care Orgs

      [] etc
      [] etc
      [] etc


           [] etc

           [] etc

</outline> Received on Sun Jul 22 2001 - 01:31:12 CEST