source: main/trunk/db/src/main/scripts/mysql/tab_c-indicator.sql @ 13418

Last change on this file since 13418 was 13418, checked in by Garth Braithwaite, 3 years ago

admin, view - finished initiatives xml, dataset and view dimension xml structs. Before moving datasets into IP xml. View added default attribution for maps.

File size: 9.9 KB
Line 
1/*========================================================================
2 *
3 * This script file contains the core Admin App's Indicator Profile related
4 * MySQL table creates (v5.0.3+). 
5 *
6 * NOTES:
7 * - MYSQL auto inserts "0" into numeric fields if no value is givin on insert or update!!!
8 * - CASE INSENSITIVE SELECTS AND UPDATES:
9 *   Use the BINARY keyword on each column you want to be case sensitive.
10 *   eg. COLUMN_X CHAR(128) BINARY NOT NULL
11 *   Then it will use case sensitive searches and selects. It is important to
12 *   note that even if a column doesn't include the BINARY keyword, all data
13 *   is still stored in the correct case.
14 *========================================================================*/
15use ibisph;
16
17
18create table INDICATOR(
19  NAME                            varchar  (100)  not null,
20  TITLE                           varchar  (250)  not null,     /* This is the selection text - NOT to be used in the graphic title creation */
21  SYNOPSIS                        varchar  (250)  not null,             /* Summary - like a title - BASE_GRAPHIC_TITLE  CORE_GRAPHIC_HEADING Is this the core text for code defined chart and map titles???????  */
22  DESCRIPTION                     varchar  (500),
23  /* */
24  DEFAULT_INDICATOR_VIEW_NAME     varchar  (200),
25  /* */
26  DEFINITION                      varchar (4000)  not null,
27  WHY_IMPORTANT                   varchar (4000)  not null,
28  HOW_DOING                       varchar (4000),
29  HOW_WE_COMPARE                  varchar (4000),       /* COMPARED_TO   was called STATE_VS_US   maybe state vs us is more descriptive or maybe we have generic for USET */
30  WHAT_DOING                      varchar (4000),
31  OTHER_PROGRAM_INFORMATION       varchar (4000),
32  OTHER_OBJECTIVE                 varchar (4000),       /* Lois said still wants this 7/27/2016 Needs specific text that an editor enters for THIS IP */
33  FOOTNOTE_REFERENCES             varchar (4000),       /* at the bottom of the entire page.  */
34  /* */
35  MORE_RESOURCES                  varchar (4000),
36  SERVICES_AVAILABLE_TO_PUBLIC    varchar (4000),   /* should this tie to a selections list???  better to do wiki links with narrative??? */
37  EVIDENCE_BASED_PRACTICES        varchar (4000),
38  /* */
39  VALUE_TYPE_NAME                 varchar  (100),  /* IPs are tied to a value type */
40  NUMERATOR                       varchar (1000)  not null,      /* NUMERATOR_NARRATIVE or NUMERATOR_DEFINITION - leaving named as is for now... */
41  DENOMINATOR                     varchar  (250)  not null,      /* this is more of a general value type description... */
42  DATA_NOTE                       varchar (1000),       /* as per Lois and Paul 8/1/2016:  DATA_NOTE: IP, DSETS, MEASURE,    ISSUES: DSOURCE and IP */
43  DATA_ISSUES                     varchar (2000),
44  ORG_UNIT_NAME                   varchar  (100),
45  OWNER_USER_ID                   varchar  (100),
46  /* */
47  DEMOGRAPHIC_FLAG                varchar    (1),               /* Used for data discovery */
48  NOT_SELECTABLE_FLAG             varchar    (1),               /* hides from selection lists and related IPs */
49  /* */
50  STATUS_CODE                     varchar    (1),
51  STATUS_DATE                     datetime,
52  PUBLISHED_DATE                  datetime,
53  /* */
54  NOTE                            varchar (4000),
55  SORT_ORDER                      int             default null,
56  ACTIVE_FLAG                     varchar    (1),
57  MODIFIED_DATE                   datetime,
58  MODIFIED_DESCRIPTION            varchar  (500),
59  /* */
60  constraint INDICATOR_PK
61    primary key (NAME)
62);
63
64
65
66/* naming convention shall be: indicatorName.viewName */
67create table INDICATOR_VIEW(
68  NAME                            varchar  (200)  not null,
69  INDICATOR_NAME                  varchar  (100)  not null,
70  TITLE                           varchar  (250)  not null,             /* used for selection - NOT part of the computed SYNOPSIS ??? */ 
71  DESCRIPTION                     varchar  (500),
72  SURROGATE_VIEW_URL              varchar (1024),                               /* Used for those special non data views.  Will need it's own IPV XSLT so that we don't loose navigation */
73  /* */
74  MEASURE_NAME                    varchar  (100)  not null,             /* This is needed for building the title and for determining which IP datasets???   discovery ??? also used to allow datasets??? */ 
75  /* */
76  CHART_NAME                      varchar  (100)  not null,
77  SUBSTITUTE_CHART_TITLE          varchar  (250),
78  CHART_NARRATIVE                 varchar (4000),
79  /* */
80  MAP_NAME                        varchar  (100)  not null,
81  SUBSTITUTE_MAP_TITLE            varchar  (250), 
82  MAP_NARRATIVE                   varchar (4000),
83  /* */
84  SUPPLEMENTAL_IMAGE_TITLE        varchar  (250),
85  SUPPLEMENTAL_IMAGE_URL          varchar (1024),
86  SUPPLEMENTAL_IMAGE_NARRATIVE    varchar (4000),
87  /* */
88  NOTE                            varchar (2000),
89  SORT_ORDER                      int             default null,
90  ACTIVE_FLAG                     varchar    (1),
91  MODIFIED_DATE                   datetime,
92  MODIFIED_DESCRIPTION            varchar  (500),
93  /* */
94  constraint INDICATOR_VIEW_PK
95    primary key (NAME)
96);
97
98
99
100/* subset of datasets for an IP VIEW based on the assoc IP name and IP VIEW Measure */
101create table DATASET_TO_INDICATOR_VIEW(
102  DATASET_NAME                    varchar  (100)  not null,
103  INDICATOR_VIEW_NAME             varchar  (100)  not null,
104  /* */ 
105  constraint DATASET_TO_INDICATOR_VIEW_UK
106    unique (DATASET_NAME, INDICATOR_VIEW_NAME)
107);
108
109/* Views are 2-d with only a series and a category.  The period at some point
110        will be allowed to be cycled through if not part of the 2-d dims.
111*/
112create table DIMENSION_TO_INDICATOR_VIEW(
113  INDICATOR_VIEW_NAME             varchar  (200)  not null,
114  DIMENSION_NAME                  varchar  (100)  not null,
115  DIMENSION_USAGE                 varchar   (20)  not null,     /* series, category */
116/*
117  SERIES_DIMENSION_NAME       varchar  (100)  not null,
118  CATEGORY_DIMENSION_NAME       varchar  (100)  not null,
119   */
120  constraint DIMENSION_TO_INDICATOR_VIEW_UK
121    unique (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_USAGE)
122);
123
124create table DIMENSION_VALUE_TO_IND_VIEW(               /* allows selection of single LHD, mult CNTY etc. */
125  INDICATOR_VIEW_NAME             varchar  (200)  not null,
126  DIMENSION_NAME                  varchar  (100)  not null,
127  DIMENSION_VALUE                 varchar  (100)  not null,
128  SORT_ORDER                      int             default null,
129  /**/
130  constraint DIMENSION_VALUE_TO_IND_VIEW_UK
131    unique (INDICATOR_VIEW_NAME, DIMENSION_NAME, DIMENSION_VALUE)
132);
133
134
135
136The above works even for views where period is the category.  Maybe if no 
137period/category dim values specified you get them all????  Or maybe a flag???
138
139
140PERIOD DIM VALUES ARE WITH THE DATASET !!!!!!!!!!!!!!!!!!
141CATEGORY AND SERIES DIM VALUES ARE WITH THE VIEW!!!!!!!!!!!!!!!!!!!!
142
143allows the editor to choose which DIMENSION VALUEs to be used for the view
144******** However, this is not needed as you always want the entire data cube.
145If there are missing values then the dataset value(s) should be tagged with the
146appropriate attribute as such.
147
148Where it is TRICKY: all years 1980-2040.  For a view you only want to see 2000-2016
149Could simply rely on the DATASET_RECORD.ACTIVE_FLAG to control this.  After all
150the DS is 100% tied to an IP.  This *MIGHT* limit the outside trending usage
151but greatly simplifies the UI for the editor.  An editor simply defines the IP
152DS and its values.  Then they define which DIMs and DIM VALUES and their usage.
153Then they simply popuplate the approp DS RECORDS and they are done.
154
155
156
157
158
159
1601:n for saved queries to IPV
161
162these are technically associated with a SUBSET of DATASET (which also has SAVED_QUERY_URL)
163Not sure if this is needed other than flexibility???
164
165if implmenting then need intersection table.
166probably should be more generic - if a view then the IV table has the surrogate
167URL.  If dataset query then should be generic?
168create table INDICATOR_VIEW_SAVED_QUERY(
169  INDICATOR_VIEW_NAME             varchar  (100)  not null,
170  TITLE                           varchar  (100)  not null,
171  URL                             varchar (1024)  not null,
172  /* */
173  SORT_ORDER                      int             default null,
174  /* */
175  constraint INDICATOR_VIEW_SAVED_QUERY_UK
176    primary key (INDICATOR_VIEW_NAME, SAVED_QUERY_NAME)
177);
178
179
180
181
182/* table that stores the valid editors for for a given indicator */
183create table USER_TO_INDICATOR(
184  INDICATOR_NAME                  varchar  (100)  not null,
185  USER_ID                         varchar  (100)  not null,
186  /* */
187  constraint USER_TO_INDICATOR_UK
188    unique (INDICATOR_NAME, USER_ID)
189);
190
191
192
193
194
195/* table that stores the text for a given indicator's relation */
196create table INDICATOR_TO_RELATION(
197  INDICATOR_NAME                  varchar  (100)  not null,
198  RELATION_NAME                   varchar  (100)  not null,
199  TEXT                            varchar (4000)  not null,
200  /* */
201  constraint INDICATOR_TO_RELATION_UK
202    unique (INDICATOR_NAME, RELATION_NAME)
203);
204
205/* intersection table that ties indicators to other indicators for the given relation */
206create table INDICATOR_TO_INDICATOR(
207  INDICATOR_NAME                  varchar  (100)  not null,
208  RELATION_NAME                   varchar  (100)  not null,
209  RELATED_INDICATOR_NAME          varchar  (100)  not null,
210  /* */
211  constraint INDICATOR_TO_INDICATOR_UK
212    unique (INDICATOR_NAME, RELATION_NAME, RELATED_INDICATOR_NAME)
213);
214
215
216/* intersection table that links IPs to TOPICs.  */
217create table INDICATOR_TO_TOPIC(
218  INDICATOR_NAME                  varchar  (100)  not null,
219  TOPIC_NAME                      varchar  (100)  not null,
220  /**/
221  SORT_ORDER                      int             default null,
222  /**/
223  constraint INDICATOR_TO_TOPIC_UK
224    primary key (INDICATOR_NAME, TOPIC_NAME)
225);
226
227/*------------------------------ END OF FILE ------------------------------*/
228
Note: See TracBrowser for help on using the repository browser.