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 LN
| 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. |
| 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. |
|
||