ACUITY DB mapping tables

Mapping tables system is probably the most complicated in the ACUITY DB structure. It is used mostly by AdminUI to define the rules of data uploading from various source files to the event tables, but also by VAHub and in some cases VASecurity. There is many tables with static data between them.

High-level business entities

• map_project_rule - this table contains projects (drug programmes) common mapping data.
• map_clinical_study - this table contains clinical studies common mapping data (unlike result table system, here there is a separate storage for clinical studies)
• map_study_rule - this table contains datasets common mapping data (despite the name)

Files to result entities mapping.

The main idea here is that source files (CSV-like, with rows and named columns) are parsed, and the result is received as business entities (and then written to the DB tables), but column (field) set and names are different for files and entities (tables): some columns just have different names, some source files can correspond to more than one entity (or opposite), sometimes two file columns are aggregated to give one entity column value as a result etc. Mapping tables store all these rules. Business entities' structure is fixed, but files structure may be unique for each dataset, so it takes for correct data upload to setup mapping rules for this dataset.

Files data

• map_file_description - file descriptions (types of information) existing in the system. Types of information are kinds of input data depending on which real source file should be parsed differently: e.g. one way for Visit source, another for Adverse Event source etc. Static data.
• map_file_section - each of type of information (entry of map_file_description) belongs to some section: it may be Mandatory patient information, Patient safety information, Oncology specific information etc. This table stores info about sections. Static data.
• map_file_rule - info (fullname, format etc) about real source files placed in the Azure storage or local file system.
• map_file_type - possible source file types (formats). Each map_file_rule entry belongs to one of these types and is linked with one of map_file_type entry through the FK field mfr_mft_id. Static data; for now, there is only two file types: text file with separated values (CSV, in fact) and SAS file.
• map_description_file - a link table map_file_description and map_file_rule. It implements the many-to-many linking between these table that allows to use the same source file in two different ways if needed... and, of course, to have different files treated in the same way for different datasets.
• map_column_rule - info about a column in a source file. Entries are linked to map_mapping_rule entries, not directly to map_file_rule entries.

Entities data

• map_entity - types of business entities (result of data upload process). For each entry in this table there is in DB a corresponding result_... table. Static data.
• map_field - fields of business entities (stored in map_entity table). For each of them, there is a corresponding field in some of result_... tables. Static data.
• map_field_description - descriptions of entity fields (entries of map_field). In many cases, several fields belonging to different entities can be described identically (e.g., Visit number field). Such fields (entries of map_field) are linked to the same map_field_description entries. Static data.
• map_dynamic_field - dynamic fields of business entity. Dynamic fields, unlikely to regular fields, has no predefined names and specific table columns to store values. Currently, they are used only for custom patients grouping for biomarkers plot. Non-static data!

Files-to-entities mapping itself

• map_description_entity - a link table between map_file_description and map_entity, implementing many-to-many linking between them. Some types of information are uploaded to different tables when parsing, and some of these tables are specific to this particular type of information, but other are common (like result_event_type and result_test), so many-to-many linking is required here. Static data.
• map_aggr_fun - aggregation functions that unite two source file column values to one business entity field value in some way: it may be concatenation for strings, aggregation of date and time to datetime value, array-based aggregation etc.; there is also default No aggregation value.
• map_mapping_rule_field - entry of this table defines where the result of some mapping of some particular source file will be written. Links map_mapping_rule and map_field tables, but it's now many-to-many link; the only reason to have a separate table and not store this data in map_mapping_rule directly is that the field may be dynamic and the it has different-structured data (it's stored in map_dynamic_field).
• map_mapping_rule - the main table linking source file columns and business entities fields. Ideologically one map_mapping_rule entry is a rule that defines how one business entity field value is received for some dataset. It is related with FK to a map_file_rule entry — it's the file to which this mapping is applied). It is FK-linked by map_column_rule entries - these are source file columns that participate in the resulting value calculation. The calculation itself is defined by map_aggr_fun entry that is FK-pointed by corresponding map_mapping_rule field. Entity field where the calculated result will be written is defined either by a map_mapping_rule_field entry or by a map_dynamic_field entry FK-pointing to the map_mapping_rule entry.

Subject grouping mappings

• map_subject_group_rule - additional subject groupings that are set up manually from AdminUI ("Setup alternative subject groupings" tab in AdminUI). They are "alternative" because the regular source of subject grouping information are source files (Patient group information information type). So on the DB level map_subject_group_rule is an alternative to result_patient_group table; difference is that map_subject_group_rule is filled before the data upload, and result_patient_group filling is a part of the upload process.
• map_subject_group_value_rule - subjects membership in groups from groupings listed in the map_subject_grouping table. Each entry of the map_subject_grouping_type table describes participation of a subject in a group of a grouping; there is no other separate place where these groups are somehow described.
• map_subject_grouping_type - list of subject grouping types. Static data. There is currently three grouping types: DOSE, NONE (also known as "Other") and BIOMARKER. Biomarker groupings are special and are used in different way than Dose and Other.
• map_subject_grouping - selection state of subject groupings. A grouping may be selected as Dose grouping, or as Other grouping, or as Biomarker grouping (it depends on msg_msgt_id field value, and msg_grouping_selected field value is just true), or just to be unselected (then msg_grouping_selected field value is false or just there is no entry for this grouping). It's quite strange solution, but...

Custom events grouping mappings

• map_ae_group_rule, map_lab_group_rule - tables for custom events groupings (each entry is a group and contains info about grouping to which it belongs). Currently there is such functionality for AE and Lab event types only. These groupings are set up on the project level and refer to projects. Likely all these 6 tables will be removed (looks like they are not really used in VAHub)
• map_ae_group_value_rule, map_lab_group_value_rule - tables for custom event group assignments.
• map_study_ae_group, map_study_lab_group - custom event groupings set up for a project may be selected for particular datasets — such a selection is defined by this table entry ("Select custom project groupings" tab in AdminUI)

Other mappings

• map_excluding_values - this table stores exclusion values for some field of some business entity of some dataset. In case when this entity field in dataset has this value, the entity won't be written to the result table during the upload procedure.
• map_study_baseline_drug - data about what drugs should be included in the baseline calculation for the story ("Determine how baseline values are calculated" tab in AdminUI)
• map_custom_labcode_lookup - data about custom labcodes mappings ("Setup alternative labcode decoding information" tab in AdminUI)