Suggestion for Tooth Chart Data Structure

Question

Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database. I had first this thought of making a data structure like this

teeth_table 
teeth_id (Primary key)
teeth_1 (These are 32 tooth)
until teeth_32 (These are 32 tooth)
patient_id (Foreign key connected to the patient table)

my second plan is to make an individual table for each tooth (which will take a lot of time I guess?)

tooth01_table
tooth_id
tooth_name
tooth_condition
tooth_recommendation
tooth_treatment
patient_id

and that's all I thought, my plan is to make a table where it would show the following when a patient selects a tooth from a dropdown list

Tooth | Condition | Recommendation | Treatment

So what do you guys think is a good design for a tooth chart database?

a tooth chart is like these

http://www.mouthandteeth.com/img/FDI-tooth-numbering-system.gif

score 1 · Answer 1 · answered Oct 09 '14 at 17:17

I would so something like this. I presume that your patients will visit more than once and you will need a data set for each visit. Of course, you will need to add more columns, but this structure should allow for flexibility for reporting as well as data entry.

CREATE TABLE Teeth
(
ToothID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, ToothPosition INT NOT NULL
, ToothType VARCHAR(10) NOT NULL
)

CREATE TABLE Patient
(
PatientID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientName VARCHAR(100)
)

CREATE TABLE PatientVisit
(
PatientVisitID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, PatientID INT NOT NULL REFERENCES Patient(PatientID)
, DateVisit DATETIME2 NOT NULL
)

CREATE TABLE PatientTeeth
(
PatientTeethID INT PRIMARY KEY NOT NULL IDENTITY(1,1)
, PatientVisitID INT NOT NULL REFERENCES PatientVisit(PatientVisitID)
, ToothID INT NOT NULL REFERENCES Teeth(ToothID)
, ToothCondition VARCHAR(10) NULL
, ToothRecommendation VARCHAR(10) NULL
)

score 0 · Answer 2 · answered Oct 27 '14 at 11:56

Although the number of teeth is nominally fixed in adults, there are pathologies which cause a higer or lower number. Your first thought would not allow for this. So I would go with the second approach. This has the flexibility to deal with normal adult teeth, milk teeth and abnormal circumstances.

I would suggest these tables.

Patient
    ID
    Name
    Address etc.

Examination
    ExaminationID
    PatientID
    DateTime

ToothCondition
    ExaminationID
    ToothID        -- using the naming scheme from your linked image
    Condition      -- missing, erupted, crowned etc.
    Recommendation
    Treatment
    IsMissing      -- Yes/No

AdultTooth
    ToothID        -- using the naming scheme from your linked image
    SortSequence

MilkTooth
    ToothID
    SortSequence

For each new Examination copy forward all ToothCondition rows from the previous one. The AdultTooth and MilkTooth tables are just convenience sets which can be copied to Examination for the first examination.

I have separated IsMissing as a special case of Condition to make it clear that it is the tooth that is absent and not the row in the table!

Suggestion for Tooth Chart Data Structure

2 Answers2