databasemedical

Database design for dentist app

So I'm doing a web app for my sister, as she recently received her degree on odontology.

I'm having a bit of a struggle with the database design though...

I'm obviously supposed to have an appointment table (tbl_appointment), appointments may have treatments associated to them, and those treatments all have different attributes (columns).

So I'm thinking I should have a tbl_appointment and also a tbl_treatment, I'm not sure on how to link those tables as to have multiple treatment types, with different treatment parameters and save the treatment data related to the appointment.

What do you think would be a good approach to this? I already have something drawn out but it definitely doesn't seem good as I end up having one table for each treatment type and I don't think thats the best solution.

EDIT

As this wasn't clear enough, here's a screenshot of how my actual db looks like.

If I implement the treatments as I did the periodontogram, I'm gonna end up with 20 tables one for each treatment type. I want to avoid that!

enter image description here

EDIT

@Ian Kenney, so if I got what you were suggesting right, this is how the portion of the database we're discussing should look like...

enter image description here

Am I right? Don't pay attention to the relation types as they're all 1:1, I am aware I should use some M:N in there, but it was just for the example.

Solution

  • One approach for the various treatment types would be to look at the attributes that are common between them, for example it may include:

    These become the columns of tbl_treatment

    Then use an additional table for other (treatment specific) attributes tbl_treatment_attributes with a structure like:

    Each treatment could have many additional attributes, the acceptable attributes (including defaults) could be managed in tbl_treatment_defaults

    EDIT

    +-------------------+              +--------------------+
| tbl_treatment     |              | tbl_treatment_type |
+===================+              +====================+
|*treatment_id      |              |*treatment_type_id  |
|+treatment_type_id |<-------------| treatment_name     |
|  ......           |              | ......             |
+-------------------+              +--------------------+
         |                                   |
         v                                   v
+--------------------------+       +------------------------+
| tbl_treatment_attributes |       | tbl_treatment_defaults |
+==========================+       +========================+
|*treatment_id             |       |*treatment_type_id      |
| attribute_name           |<------| attribute_name         |
| attribute_value          |       | default_value          |
| ........                 |       | .......                |
+--------------------------+       +------------------------+