I wrote a song about dental floss but did anyone’s teeth get cleaner?
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_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
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
name– the patient’s name
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
address– the patient’s address
phone– the patient’s phone number
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
patient_id– is the patient’s id related to his visit
dentist_id– is a reference to
user_has_roletable, 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
anamnesistable, which also references the
user_anamnesis_id– this relates to the
user_has_roletable. 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.
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.
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_id– is a reference to 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_catalog_id– is a reference to the
Note that the
visit_anamnesis table is a many-to-many relation connecting the tables labeled
anamnesis_catalog. There is no point to store this pair (
anamnesis_catalog_id) twice. We’ll use that pair as the primary key.
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
location– contains exact document location
patient_id– is a reference to 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.
tooth– is a description used for the tooth getting work done – generally, that value will be shown on-screen.
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.
problem_detected table connects visit, tooth, and problem catalog data with the
treatment table. It contains references to the
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_idis a reference to the
treatmenttable (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_idis another reference to the
treatmenttable. 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
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.
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
description– is a description of the actions taken during this step
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
description– is a short treatment description
final_step_id– is a reference to the
steptable. 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.
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
step_id– is a reference to the
step_order– is a number that defines the order of steps in treatment
In this table two alternate keys (UNIQUE) are defined:
- pair (
step_id) – this step can be assigned to the treatment only once
- pair (
step_order) – the treatment can’t have two steps with the same order number
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
notes) are as follows:
visit_id– is a reference to the
treatment_steps_id– is a reference to the
problem_detected_id– is a reference to the
problem_detectedtable. 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
notes– are notes for that step, if needed
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.
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
visit_status_id– is a reference to the
visit_id– is a reference to the
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_stepstable for future events)
- document handling
Still, it makes you think differently about your dental office and its procedures, doesn’t it?