Create ER diagram framework on Hospital compare data files

[ninjapapa]

Use docs/design/exampleER.puml as a template, create an ER diagram framework for Hospital Compare data files.

No need to have all columns covered in each table, just need to have the tables with some key columns in them.

This work will give us an overall view of the relationship, then later when we really finish schema discovery, we can add all the column names back to the plantUml file easily.

[jackyact]

A draft puml updated, with all data categories and most of tables / columns included. Linkage is not built yet. Will work on linking tomorrow. Could you please quickly review and comment?

A few questions:

• How to define a "package" for the data? Within one "package", need we link all tables with the same key (eg, CCN)?
• The data from website is raw in that:
  1. Most of tables are combined "fact" tables with dimension tables. Eg, both measure_ID and measure_Description exist in the same table. Should we do ETL beforehand?
  2. The format of variable are mostly string. Should we change into a meaningful variable type? Eg, the column "percentage" has numbers and "Not Available". So it is labelled as "String" in the raw data though changing to “Double" makes it more readable.
  3. The granularity and structure of tables are different - some are long tables and others are wide. Shall we do standardization before merging to the "hospital master"?

[ninjapapa]

The ER diagram we typically create are only for summarized the knowledge. It does not need to be very strict. So here are the answers:

• Group tables together if you think they should be grouped, otherwise, just don't need to use "package"
• 1. No need to really separate the fact table and dimension table, the drawing difference could be just rough and in some cases the table don't need to be either fact or dimension. We don't need to normalization in this stage. May need to discuss how to create the master later.
• 2. could create a "md" file to document all data issues. Those should be fixed in the ETL stage.
• 3. same as above. sounds like we should start working on the ETL stage already.