Reference Table

From SICDB Doc
Revision as of 11:35, 9 October 2022 by Salkin (talk | contribs)
Jump to navigation Jump to search

Table Description

The d_references table contains information on all encoded data fields of the SICDB dataset. To simplify JOIN and other types of subqueries each reference value has an unique global identifier.

Raw Fields

Caption d_references
Name Description Comment
ReferenceGlobalID The unique ID for the reference Use this identifier as dictionary for alle encoded fields
ReferenceValue Reference value i.e. "male"
ReferenceName The name of the reference i.e. "sex"
ReferenceOrder Default order of reference i.e. KDIGO_0=0, KDIGO_1=1 (used in further releases)
ReferenceType Type of reference if applicable i.e. Laboratory, Medication ... (used in further releases)
ReferenceSubtype Subtype of reference if applicable i.e. Crystalloid, Colloid (used in further releases)
Caption Caption of reference May be used in future for translation
DefaultChartConfig Some data can be visualized in charts (RooDataServer), this metadata defines how
CustomFieldID Used for user-defined data (used in further releases)

Usage Information

d_references is the dictionary for all encoded data.

Raw Data

For raw data replace the value (i.e. Laboratory.LaboratoryID) with the value of d_references.ReferenceValue where Laboratory.LaboratoryID = d_references.ReferenceGlobalID

SQL

Use a join query to get the reference value.

Example:

 SELECT Sex,SexRef.ReferenceValue as SexCaption FROM `cases` INNER JOIN d_references as SexRef ON SexRef.ReferenceGlobalID = cases.Sex;

RooDataServer

ReferenceSelection

All fields using references provide simple filters to select references. On export RooDataServer will per default export the text value of an encoding.



Note

To reduce the amount of tables there is no table describing references as there is no significant gain of information expected. This may change in later versions of dataset. To enumerate a reference use table d_references.ReferenceName, which is an immutable, case sensitive key to a reference.