Design a Therapy Clinic with MySQL

Arturo Arriaga
May 10, 2022
1 min read

Project Description:

This database models the functionality of a therapy clinic and was created using MySQL hosted in an EC2 instance on Amazon Web Services. The database has 9 tables populated with 14,000+ data entries, shared between 5 locations, 35 therapists, 150 clients, 7180 sessions and 7180 generated billing records for each session.

**The inputs for this database was generated using generatedata.com. **

Schema Design

Entities: The main entities of this project are:

Clinics – A location where services are performed.
Therapists – A clinician and information related to their credentials, specialty and language.
Clients – A client that the clinic serves.
Sessions – A therapy session and relevant information.
Billing Records – A record that maps to a therapy session used by the clinic to bill for services.
Languages – The languages used by therapists and clients.
Diagnosis – The diagnosis of the client. Maps to the specialty of a therapist.
Billing Codes – The codes represent the services of the clinic.
Billing Status – The status of a billing record describing the billing status if a session.

Relationships: The relationships between entities:

Therapists and Clients – both share a relationship to the languages and diagnosis tables. The therapist has a specialty and can deliver treatment in a language that can be mapped to a client.
Sessions and Billing records – the billing records the clinic uses to bill for services.
Sessions, Locations, Therapists, Clients – the clinic where a service occurred, the treating therapist and the client.