Database Modeling

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
• 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
• 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
• patient_id – 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
• user_anamnesis_id – this relates to the user_has_role 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
• 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
• 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
• 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.
• 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.
• 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
• 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
• 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
• step_id – is a reference to the step 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
• 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:

1. We may have chosen a treatment, but we don’t need all the steps defined for it, and
2. 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
• treatment_steps_id – is a reference to the treatment_steps 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.
• step_time – 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
• visit_status_id – is a reference to the visit_status 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)
• document handling

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