Star Schema for Surgeries

I have the following tables:

PATIENTS: Stores data about Patients,
ADDRESSES: Stores addresses of patients.
ADDRESS_TYPE: The address of a patient can be of three types, home, work and vacations.
MEDICS: A list of all the medical staff in the hospital. Any of them can be a surgeon, or a helper.
MEDIC_ROLE: A medic can either be a surgeon or a helper in a surgery.
SURGERIES_LIST: A look table for surgeries.
PATIENT_SURGERY: This table stores data about patients' surgeries.
STAFF_INVOLVED: This table stores information about all the staff involved in a surgery. There can be one or more surgeons and zero or more helpers.

The model can be seen on the following Link:
Link: RDB Tables

I need to build a star schema, which is able to answer the following questions:

To understand which kind of surgeries are performed more in which month of the year, to find out if there is a relation in weather and the disease.

Which kind of surgery is performed the most on people from a specific area, to establish a relation with the habitat of the area.

To know the number of surgeries in which a surgeon or a helper participated in a specific period of time e.g. week, month, year.

To know which kind of surgery is more common in which age group.

To know how many patients needed the same surgery more than once i.e. to understand the effectiveness of a specific surgery procedure.

I came up with the following star schema from the above relational model.
Link: Star Schema with Bridge Table

My questions are:

Do you think I will able to get answers of the above questions in a good manner?
How can I improve this design?

This is how you can improve you model:
1) you need a age group dim. Making age group part of patient dim will not work.
2) There is 1 to many relationship between a patient and many ICD9. A patient having ENT surgery (Ear, nose, throat). Address it with a bridge table.

rf001 wrote:My questions are:

Do you think I will able to get answers of the above questions in a good manner?
How can I improve this design?

Hi

If you use AGE attribute in PATIENTS dimension, you will have to use a process to update every patient when he/she gets older which sometime is not a good idea. Depending on how you are going to present the data, there are some other options:
1. Calculate the age in the reporting tool (surgery date - date of birth).
2. Have a degenerate dimension in the SURGERIES fact table.
3. Create a separate dimension AGE which will have records from 1 to 150 or 200 just in case and the age group as an attribute.

Not sure I understand why you need MEDIC_BRIDGE table. If a medic can have only one role, I would denormalize the MEDICS dimension and have the role as an attribute. Otherwise, I would create 2 dimensions - MEDICS and MEDICAL_ROLE and have 2 FK's in the SURGERIES fact table - MEDIC_ID and MEDICAL_ROLE_ID.
Also, I would recommend checking if there is a need for slowly changing dimensions if the history is required.

Thanks,
Alisher
www.streebo.com

Thanks for replies.

1. Age means age at the time of surgery i.e. Age of a patient will change with the passage of time, however age at the time of the surgery will not change.

2. A medic can have one role in one surgery but another role in another surgery i.e. A medic may be Surgeon in one surgery but a Helper in another.

3. There is one entry for one surgery at a time, if multiple surgeries are performed, multiple rows are inserted.

Considering the above, do I still need to change something?

rf001 wrote:1. Age means age at the time of surgery i.e. Age of a patient will change with the passage of time, however age at the time of the surgery will not change.

The star schema you design will not be able to handle this requirements. Age data should be part of transactions. You can use one of the recommended options.

rf001 wrote:2. A medic can have one role in one surgery but another role in another surgery i.e. A medic may be Surgeon in one surgery but a Helper in another.
3. There is one entry for one surgery at a time, if multiple surgeries are performed, multiple rows are inserted.

MEDIC_BRIDGE is not necessary. As I said before, a better way would be to use 2 dimensions - MEDICS and MEDICAL_ROLE.

MEDICS (ID as PK, list of medics) <------ SURGERIES (facts with MEDICS_ID as FK to MEDICS and MEDICAL_ROLE_ID as FK to MEDICAL_ROLE) ------> MEDICAL_ROLE (ID as PK, list of roles)

Thanks,
Alisher
www.streebo.com

I am going to make the changes.
Thanks for your help...