Oregon Cryonics
A Non-profit Organization

Database Schema

back to Software

 

The Open Dental database was designed for dental offices, but works quite well for our purposes.  The generic schema is fully documented here. Tables that we have added are documented below.  We access these tables through a custom C# Software interface written in house and installed as a plugin to Open Dental.

 

occontainer

A Container row can either be an actual container or one sample of patient tissue currently inside a container. Full nesting is supported. Historical changes are not retained in the database, but are instead archived with paper copies and with audit trail entries. There can be multiple patients in one container, and multiple subcontainers within another container. A container cannot be deleted if it currently has any subitems or if it is referenced by a FK from another table. 

 

Column Type Summary
ContainerNum bigint(20) Primary key. These are visible to and used by staff, unlike most primary keys. This compromise is made so that queries and db analysis can be easier. 1 through 199 is reserved for dewars. Other containers start at 200.
ParentContainer bigint(20) FK to occontainer.ContainerNum. The container that this item is directly inside of. Root-level containers will have this value be zero.
PatNum bigint(20) FK to patient.PatNum. Only if this is a patient within the container. Will be zero if this is an actual container. If there is more than one entry in this table per patient, it indicates that tissue from the patient is in multiple containers.
DisplayText text The text that summarizes this row, and which will be displayed to users. Does not include leading spaces which would indicate heirarchy level. Also does not include trailing note. Examples:2-Dewar or 123-Smith, John
Notes text Any notes regarding this container or patient.
HeirarchyLevel int Root level containers, such as Dewars, are at level 0. Other containers or patients are nested at various other levels, probably 1 or 2.
ItemOrder int The order to display this row in the grid. 0-based. All containers are part of the ordering, even hidden ones. When hidden, a container is moved down below non-hidden containers.
ContType tinyint(4) Enum:ContainerType 0=Patient, 1=Dewar, 2=Neurocan, 3=Hdpe.
IsHidden tinyint(4) When a container gets removed from service, but can't be deleted due to FKs, then this gets set to true so that it will no longer show in the grid.
MinLevel float In inches from the top. This is the absolute minimum level. Alarm is also tied to this level.
MaxLevel float In inches from the top. This is the level marked within the dewar as max. Lid may barely float at this level.

 

oclevel

An entry is made in this table when an employee checks the level of a dewar or adds LN2 to a dewar. Also used to record checking on chemical fixation patients or changing solutions. User can only delete an entry that was made today by themself.

Column Type Summary
LevelNum bigint(20) Primary key
ContainerNum bigint(20) FK to occontainer.ContainerNum. The container that is being checked or filled.
DateTEntry datetime The date and time of the entry. Cannot be backdated, so in the case of a forgotten entry, add a note of explanation.
UserNum biging(20) FK to userod.UserNum.
EntryType tinyint(4) Enum:LevelEntryType 0=LevelCheck, 1=Fill,2=FixativeCheck,3=FixativeChange.
LevelStart

float

Inches measured from top of dewar. For a LevelCheck, this will be the same value as LevelEnd.
LevelEnd float Inches measured from top of dewar.
Notes text Any notes or observations.

 

ocmember

1:1 relationship to patient table.  Patients will not have an entry in this table unless they have set up a web account.  No member is entered into this table without also being entered into the patient table.  The name and BD is John Doe, 1/1/1900 unless they change it.  To prevent a social engineering privacy attack, we never manually connect a member to an existing patient.  If a member forgets their password, we can email a new one to the email on file. Members who demand very high privacy should leave their name as John Doe.

Column Type Summary
MemberNum bigint(20) Primary key
PatNum bigint(20) FK to patient.PatNum.  Mandatory and never 0.
Email varchar(255) Email not validated
PassHash varchar(255) Hash of password
DateMembership date

Date that the member created their account

DateInactive date Date that membership was set to inactive.  Might even be same date as membership if they are not serious.