Schema: Dynamic Tables

Background

How Dynamic fields work in entering/viewing:

Overview

One of the main goals of TMAJ was to be able to keep track of different data for different organ systems.

Very Simple Approach

This describes a very simple way of keeping track of dynamic data that we did NOT use. This is supposed to give you an idea of what TMAJ is trying to do.

Suppose you had a table Patients, and some patients were breast cancer patients, and others were prostate cancer patients. You would have some data that would only be relevant for prostate cancer patients, and vice versa. Here is a one simplistic database design:

Patients Table
PatientID
PatientName
Hospital
BirthDate
Sex

PatientData Table
PatientID
Key
Value

So we have common field names in the Patients table: PatientName, Hospital, BirthDate, and Sex. These fields names we want to know for every patient. But there is other type of data that would be only relevant for certain types of patients. For example, the data field ProstateWeight would be irrelevant for a breast cancer patient. In the PatientData table, we would have data specific for certain types of patients. For example, for a prostate cancer patient, with PatientID#55, we would record a record:
PatientID: 55
Key: ProstateWeight
Value: 15 grams

TMAJ's Approach

The problem with the above approach is that the meta-data is stored in a very poor format. Meta-Data is defined as data about data. In the above example, the meta-data would be the different types, like prostate cancer and breast cancer, and the different fields we record for both of them, like ProstateWeight. TMAJ definies a strict, hierachal way of storing meta-data.

First, we start with the type. So using the above example there are two types: prostate cancer and breast cancer. The types are the top of the hiearchy. The next item down are the fields. Types may have many fields. So prostate cancer might have fields: ProstateWeight, ProstateCancerStage, and ProstateFluidPresent. The next item down are enumerations. Optionally, fields may be enumerated. So the field ProstateCancerStage might have enumerations I, II, III, IV, and V.
So is summary the hierachy of the meta data is:
Types --------> Fields --------> Enumerations

Specimens Section

Specimens Dynamic Structure. The fields common for every specimen are seen in the Specimens table. For example, the SurgPathNumber and PatientID are fields we would want to know for every specimen, no matter what the SpecimenType is. Notice the SpecimenTypeID field in the table Specimens. It is important that every Specimen is assigned a SpecimenTypeID. Depending on the specimenType selected, different specimenFields will be presented. Optionally, the fields may be enumerated (SpecimenEnums Table). The SpecimenData table holds the dynamic-data. For the values that are typed-in (not enumerated), FieldValue is used in SpecimenData. If the data is enumerated, then EnumID is used.

The Meta Data in this diagram is shown by 3 tables: SpecimenTypes, SpecimenFieldNames, and SpecimenEnums. Each SpecimenType may have many SpecimenFieldNames, and each SpecimenFieldName may have many SpecimenEnumerations. This illistrates the hierarchy of the Meta Data in TMAJ
Type ---> Field ---> Enumeration

Tissue Diagnosis Section

Tissue Diagnosis Structure. Identical to the design of the Specimens Dynamic Section, except the names are different.

Scored Images Section

Scored-Images Dynamic Structure. This structure is almost the same as the previous two. There is a slight variation here. The only difference is the fields presented to the user not only depend on type (TissueType), but on the ScoringStrategy of the session they are using. (Sessions are optionally assigned at most one ScoringStrategy.) If a session is assigned a ScoringStrategy, any dynamic fields (in ScoredImageFields) associated with that ScoringStrategy will be presented for that user as well. Also, you may have noticed that ScoredImages and TissueDiagnosis both share the same types table (TissueTypes), while the Specimens table has its own type table (SpecimenTypes).