Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

gary8877 (IS/IT--Management) (OP)
15 Aug 06 16:04
I am exploring the design options for the dimensional model of Health Care System. In this design we don't have any financial data or any sort of concrete measurements. The business process works as follows:

The patient enters into the Hospital or Health Services Center and answers some questions on an assessment form. And by the answered questions, it is determined what kind of services are needed by the patient. This entire process is termed as "in-take assessment" and focuses solely on assessment.

Mostly the reports will be measuring the count of assessments based on various dimensions. In this model we have Patient, Assessing Organization, Diagnosis (multivalued), Time, User (one who assesses), Assessment Type, Care Product, etc. as Dimensions and the Actual Assessments as the Factless Fact. The assessment process form consists of objective questions(defined values/radio button choices - mostly int or string) and open-ended questions (free text). There are approximately 100 questions in the form. The reporting criteria(where clause) can be one of the above mentioned dimensions or may be one of the answers given by the patient in the form. If I create one dimension per Q/A it will be a centipede design with too many dimensions.

Now my question is

*whether the assessment Fact should contain all answers in one row in different question columns (in which case, it will be a very big table - approximately 120 columns in one row with 30,000 Assessments each month) or

*should the questions be in a dimension, in which case the dimension will be as large as the fact, in terms of rows. It will act more like a fact table. It is like having a Primary Key in the Fact table Assessment and Foreign Key in the Assessment Q/A table or

*should I try to come up with various combinations of possible answers for these questions and then put them in a dimension, in which case the dimension again will be very large or

*should I distribute the questions in combinations of 20 each and then apply possible answer combinations in 5 different dimensions or

*should I create 1 dimension per questions/answer (centipede design)?

Any thoughts/suggestions/guidance will be really appreciated.
RiverGuy (Programmer)
15 Aug 06 16:58
I would start off with something like this initially:

Assessment dimension:  Assessment ID, Time, User, etc.

Question dimension:  Question ID

Diagnosis dimension:  Diagnosis ID

Answer dimension:  Answer ID

Question fact: Assessment ID, Question ID, Answer ID, Answer Text (using a 0 Answer ID for free text)

Diagnosis fact: Assessment ID, Diagnosis ID
johnherman (MIS)
15 Aug 06 17:33
The assessment looks like a survey. Typical survey type data structures should suffice. Examples of tables in survey data structures.

Survey
SurveyResponder
SurveyQuestion
SurveyResponderAnswer
Question
QuestionValidAnswer
ResponderInfo

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

gary8877 (IS/IT--Management) (OP)
16 Aug 06 14:19
Thanks for answering my question. So are u both suggesting to go with 1 row per assement question. In this case the fact will end up with 100 rows per assessment. And each patient is assessed multiple times, lets assume avg 5. So I will end up with 500 rows per patient. And they normally assess 30 thousand people per month. So this means 500*30,000 = 15000,000 rows per month and 15000,000 * 12 = 180000000 per year. This design is good in terms of only two joins. But in terms of no. of rows, performance might still be a problem. Any thoughts?
johnherman (MIS)
16 Aug 06 15:07
You could consider keeping the most recent assessment(s) in one table, and the earlier ones in another; available "joined" via a UNION view. Alot depends on what kinds of queries you are expecting.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

RiverGuy (Programmer)
18 Aug 06 8:26
If you're worried about the performance, a fact table is a good candidate to randomly populate 180,000,000 rows - since you're mostly concerned about a few numerical values as opposed to string data.   Populate a test table, and try out the performance.  Depending on your DBMS, you may also have other alternatives such as partioned tables.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close