I wrote a song about dental floss but did anyone’s teeth get cleaner? Frank Zappa

When we think of the dental office, our first associations are the drill, the pain, and the fear. OK, that sounds bad. Besides taking care of teeth, a dentist has many other obligations that are professional, legal, or both. All of them require proper data management.

To meet this documentation requirement, many dental and medical offices use paper records. Slowly but surely, though, there’s a trend towards the digital records and management of the 21st century.

Inside the Office of Dental Medicine

Going to the dentist is not something we usually remember with joy. If we were lucky, we dodged the pain but our wallet probably suffered badly. After we step into a dentist’s office, the procedure is usually as follows:

You both engage in a short bout of chit-chat. (Often unpleasant because you promised your dentist you’ll visit him next week and 2 years have passed. Then you say you forgot, he agrees, and everything is ok again.)

You sit on the chair while he looks at your previous treatment records. He asks if something happened since last visit and is there any update to your medical record.

He takes a look inside your mouth to determine what went wrong, and tells you what will fix it.

You can agree with his treatment plan or choose some other option.

A few months later, a Hollywood smile is on your face again. It would have been sooner, but you couldn’t smile until you’d finally paid the bill in full for your dental work.

At this point, even the most dedicated database professional probably isn’t thinking, ‘Wow, I wish there was a data model for this experience!’. But there is, and it’s worth examining. So we’ve printed it below.

Introducing Our Dental Office Database Model

The idea behind this model is to cover every procedure from the moment we first step into the dentist’s office until the problem is solved. Part of this model (the tables labeled user_account , status , user_has_status , role , user_has_role ) was presented and described in previous articles. Maybe roles and statuses look unnecessary here, but remember that the practice could also contain a nurse to handle the anamnesis (record-taking), a receptionist, a dental student, several trained dental assistants, or even a visiting specialist or a hygienist. However, payment details won’t be considered in this article.

Tables in the Dental Database

The patient table is one of two most important tables in database. It stores patients’ data and is connected to patients’ documents and visits. With the exception of mail , all the attributes in the table are mandatory:

name – the patient’s name

– the patient’s name surname – the patient’s surname

– the patient’s surname identification_number – this field is used to store a client’s unique id that is used in the real world

– this field is used to store a client’s unique id that is used in the real world address – the patient’s address

– the patient’s address phone – the patient’s phone number

– the patient’s phone number mail – the patient’s mail address

The second-most-important table in database is visit . When combined with the table patient , it stores information about the event that triggered all of the subsequent actions. The attributes in the table are:

visit_date – contains the actual date and time when visit has occurred

– contains the actual date and time when visit has occurred patient_id – is the patient’s id related to his visit

– is the patient’s id related to his visit dentist_id – is a reference to user_has_role table, assuming that the role is dentist

Next up is the anamnesis table. In medicine, anamnesis is a procedure where we collect and store medical data history, such as the patient’s current condition. The anamnesis table stores this data. Since this happens soon after our arrival in the office, we’ll have at most one anamnesis per event. The attributes in table are:

anamnesis_id – is the primary key of the anamnesis table, which also references the visit table

– is the primary key of the table, which also references the table user_anamnesis_id – this relates to the user_has_role table. Notice that dentist doesn’t have to be the one that made anamnesis.

– this relates to the table. Notice that dentist doesn’t have to be the one that made anamnesis. notes – contains text notes about specific anamnesis. It’s not mandatory field.

The anamnesis_type table is a simple dictionary used to store all possible values that are referenced in anamnesis_catalog . The only attribute is type_name , and it can contain values like “illness”, “allergy”, “medicine used”. Of course, that sole attribute is mandatory.

The anamnesis_catalog table is dictionary that gives more specific information than values stored in the anamnesis_type table. We’ll use it to keep data about specific illness, allergies, and medications. The attributes are all mandatory, and they include:

catalog_name – is the name of specific anamnesis_type subcategory

– is the name of specific subcategory anamnesis_type_id – is a reference to the anamnesis_type table

The visit_anamnesis table is used to connect visit data with values from the anamnesis catalog. Every attribute in the table is required:

anamnesis_anamnesis_id – is a reference to the anamnesis table

– is a reference to the table anamnesis_catalog_id – is a reference to the anamnesis_catalog table

Note that the visit_anamnesis table is a many-to-many relation connecting the tables labeled anamnesis and anamnesis_catalog . There is no point to store this pair ( anamnesis_anamnesis_id & anamnesis_catalog_id ) twice. We’ll use that pair as the primary key.

The document table is a simple catalog containing locations where we have saved patients’ documents. Examples of such documents can be scans of patients’ charts, X-rays, and invoices. Of course we won’t save these documents directly into the database. This is a rude simplification of the document management system. The attributes within the document table are (all are mandatory):

description – is a short document description

– is a short document description location – contains exact document location

– contains exact document location patient_id – is a reference to the patient table

The tooth table is a simple dictionary that is used later when the dentist specifies which tooth was the problem. All attributes in this table are required. They are:

is_baby_tooth – is a Boolean value that simply marks if a tooth is a baby tooth or not. Of course, we’ll have duplicate values for teeth that can be both. This is important because a procedure may differ according to the tooth type.

– is a Boolean value that simply marks if a tooth is a baby tooth or not. Of course, we’ll have duplicate values for teeth that can be both. This is important because a procedure may differ according to the tooth type. tooth – is a description used for the tooth getting work done – generally, that value will be shown on-screen.

The problem_catalog table is another simple dictionary. It contains a list of all possible problems normally found on teeth or in the mouth. Examples of possible values for this catalog are: “tooth decay”, “tooth erosion”, “gingivitis”, “mouth sores” or “unattractive smile”. Only the problem_name attribute is mandatory.

The problem_detected table connects visit, tooth, and problem catalog data with the treatment table. It contains references to the tooth , problem_catalog and visit tables. All attributes are mandatory except for tooth_id . The reason for this exception is that some problems do not refer to only one tooth (e.g. gingivitis refers to the gums). These three attributes together form an alternate key (UNIQUE). The other two attributes are:

suggested_treatment_id is a reference to the treatment table (the treatment suggested by the dentist). It can be a NULL value when everything is OK and we don’t need any treatment.

is a reference to the table (the treatment suggested by the dentist). It can be a NULL value when everything is OK and we don’t need any treatment. selected_treatment_id is another reference to the treatment table. It contains data about the treatment dentist and patient agreed to use. This can be NULL, perhaps because the patient needs time to think about suggested treatment and other possibilities.

Note that the attributes suggested_treatment_id and selected_treatment_id are both referenced to the treatment table. We can do this because we only need to store, at most, two values. Of course, if we don’t know in advance how many values we want to store then we should use a many-to-many relation here.

The step table is a simple dictionary containing all possible steps in all treatments. The attributes (all are mandatory) in the table are:

step_name – is a short step name used on-screen

– is a short step name used on-screen description – is a description of the actions taken during this step

The treatment table is in fact a dictionary of all treatments that the dental office provides. Since most treatments usually consist of several steps, we must know which step is final. The attributes in the table are all required:

treatment_name – is the name of the treatment within the system

– is the name of the treatment within the system description – is a short treatment description

– is a short treatment description final_step_id – is a reference to the step table. We can use this information to detect if the treatment is over and initiate automatic action, or we can simply show that information to user and let him choose the next action.

The treatment_steps table is a many-to-many relation that connects steps with treatments. The mandatory attributes in the table are:

treatment_id – is a reference to the treatment table

– is a reference to the table step_id – is a reference to the step table

– is a reference to the table step_order – is a number that defines the order of steps in treatment

In this table two alternate keys (UNIQUE) are defined:

pair ( treatment_id & step_id ) – this step can be assigned to the treatment only once

& ) – this step can be assigned to the treatment only once pair ( treatment_id & step_order ) – the treatment can’t have two steps with the same order number

The visit_steps table is a list of all steps that were actually conducted after that visit. There are two reasons why we want to store them in separate tables:

We may have chosen a treatment, but we don’t need all the steps defined for it, and This way, we’ll store the actual time when the step was performed.

The attributes in the table (all are mandatory except problem_detected_id and notes ) are as follows:

visit_id – is a reference to the visit table

– is a reference to the table treatment_steps_id – is a reference to the treatment_steps table

– is a reference to the table problem_detected_id – is a reference to the problem_detected table. This relation gives us information about what problem initiated that action. It can be NULL when the dentist decides to take some action that is not related to any detected problem.

– is a reference to the table. This relation gives us information about what problem initiated that action. It can be NULL when the dentist decides to take some action that is not related to any detected problem. step_time – is the date and/or time when the step was actually performed

– is the date and/or time when the step was actually performed notes – are notes for that step, if needed

The visit_status table is a simple dictionary used to store all possible statuses a visit could have. We could use statuses like “first visit to office ever”, “first visit”, “treatment in progress”, “treatment finished successfully”. It contains only one attribute, status_name , which is mandatory.

The visit_status_history table is used to store data about the statuses that the visit went through. The thought is that we add status manually after certain actions are completed (e.g. after anamnesis, after finishing a few steps of some treatment). The attributes, all of which are required, follow:

status_time – is the date/time when status was inserted

– is the date/time when status was inserted visit_status_id – is a reference to the visit_status table

– is a reference to the table visit_id – is a reference to the visit table

Possible Improvements to the Dental Database Model

Our model is off to a good start, but it could be improved. For example, it doesn’t cover the following items:

payment methods and invoices

scheduling meetings (although this could be done by inserting data into the visit_steps table for future events)

table for future events) document handling

Still, it makes you think differently about your dental office and its procedures, doesn’t it?