entity_values
Summary
Values contains the measured values and are essentially a triangulation of the reporting entity, the measure being reported and the date period. The key comprises measure_id, entity_id, date_start and date_end and must be unique. footnotes_values uses this composite key as a foreign key to enforce data integrity. measure_ids and entity IDs must be created first before an entity_value can be added. Deletion from entity_values cascades to footnotes_values.
Fields
| field | type | attributes | notes | example |
|---|---|---|---|---|
| entity_values.measure_id | int(11) | NOT NULL | Part of Concatenated Key. FOREIGN KEY: links to measures | 12345 |
| entity_values.entity_id | bigint(20) | unsigned NOT NULL | Part of Concatenated Key. FOREIGN KEY: links to entities | 123456 |
| entity_values.date_start | date | NOT NULL | Part of Concatenated Key. YYYY-MM-DD | 2008-01-01 |
| entity_values.date_end | date | NOT NULL | Part of Concatenated Key. YYYY-MM-DD | 2008-12-31 |
| entity_values.source_id | int(11) | NOT NULL DEFAULT '0' | from value_sources | 12 |
| entity_values.is_comparator | tinyint(1) | NOT NULL DEFAULT '0' | Values can either be (0) reported or (1) calculated by aggregating multiple entities into comparator values, for example state average or average of the top quartile. If the value is a comparator value this field is set to "1"1 | 0 |
| entity_values.is_private | tinyint(1) | NOT NULL DEFAULT '0' | Values can either be (0) public domain or (1) private property. If the value is private this field is set to "1" | 0 |
| entity_values.value | decimal(16,5) | DEFAULT NULL | This records the value of the measure for the entity in question. | 0.5 |
| entity_values.unit | enum('percent','per 1000','per 100000','cases','ratio','days','hours','patients','dollars','na','minutes','people','square miles','years','households','per 100','per 10000','micrograms') | NOT NULL DEFAULT 'na' | The unit of measurement that the value represents | percent |
| entity_values.numerator | decimal(16,5) | DEFAULT NULL | Used for fractions, percentages, ratios etc. Signifies the number to be divided by the denominator. | 1 |
| entity_values.denominator | decimal(16,5) | DEFAULT NULL | 0Used for fractions, percentages, ratios etc. Signifies the number the numerator should be divided by. | 2 |
| entity_values.letter_grade | varchar(3) | DEFAULT NULL | Letter grades to be used in report cards. | B |
| entity_values.gpa | decimal(16,5) | DEFAULT NULL | This is used to store the mathematical value of the letter_grade. For instance, a B is equal to a gpa of 3.0 | 3.0 |
| star_rating | decimal(3,1) | DEFAULT NULL | Keeps track of the star value assigned to a row. Values can be between 1 and 5 | 4.5 |
| entity_values.sig_var_state | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant variation from the state average.
For more information about this field and the methodology used please read Statistical significance. | -1 |
| entity_values.sig_var_nation | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant variation from the national average.
For more information about this field and the methodology used please read Statistical significance. | 0 |
| entity_values.sig_change | enum('-1','0','1') | DEFAULT NULL | Subject to Minimum sample the value is tested for statistically significant improvement from the previous measurement point.
For more information about this field and the methodology used please read Statistical significance. | 1 |
| entity_values.std_dev_from_state | decimal (16,5) | DEFAULT NULL | standard deviation from state value | 1.96 |
| entity_values.std_dev_from_nation | decimal (16,5) | DEFAULT NULL | standard deviation from national value | 2.21 |
| entity_values.std_dev | decimal (16,5) | DEFAULT NULL | standard deviation for the measure set | 5.12 |
| entity_values.lower_ci | decimal (16,5) | DEFAULT NULL | lower confidence interval for statistical analysis | 1.234 |
| entity_values.upper_ci | decimal(16,5) | DEFAULT NULL | upper confidence interval for statistical analysis | 12.34 |
| entity_values.percentile_nation | smallint(3) | DEFAULT NULL | Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. | 12 |
| entity_values.percentile_state | smallint(3) | DEFAULT NULL | Subject to Minimum sample the value is percentile ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Percentile ranking. Not all values are ranked. | 34 |
| entity_values.rank_place_nation | smallint(6) | DEFAULT NULL | Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the national set. For more information about this field and the methodology used please read Competitive ranking. | 123 |
| entity_values.rank_denominator_nation | smallint(6) | DEFAULT NULL | If the value was competitively ranked, this field holds the denominator of the ranked set. | 1234 |
| entity_values.rank_place_state | smallint(6) | DEFAULT NULL | Subject to Minimum sample the value is competitively ranked against all eligible values (of the same entity type) in the relevant state set. For more information about this field and the methodology used please read Competitive ranking. | 12 |
| entity_values.rank_denominator_state | smallint(6) | DEFAULT NULL | If the value was competitively ranked intra-state, this field holds the denominator of the ranked (state) set. | 123 |
| entity_values.created | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' | The timestamp of row INSERT | 2015-01-01 12:00:00 |
| entity_values.modified | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE | The timestamp of latest row UPDATE | 2015-01-01 12:00:00 |
| entity_values.batch_id | varchar(64) | DEFAULT NULL | Denotes the data run the row was a part of | CMS20151234 |
PRIMARY KEY `entity_values_id` (`measure_id`,`entity_id`,`date_start`,`date_end`),
CONSTRAINT `entity_values_ibfk_1` FOREIGN KEY (`measure_id`) REFERENCES `measures` (`measure_id`) ON DELETE CASCADE,
CONSTRAINT `entity_values_ibfk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE