Exercise 6: Dental Clinic

erDiagram

    client {
        string name
        string phone
        string address
        int key PK "id_client"
    }

    medic {
        string name
        string phone
        int key PK "id_medic"
    }

    service {
        string name
        string detail
        decimal price
        int key PK "id_service"
    }

    appointment_state {
        string name
        string label
        string uuid
        int key PK "id_appointment_state"
    }

    appointment {
        string number
        datetime date
        decimal value
        int key PK "id_appointment"
        int id_client FK "id_client"
        int id_medic FK "id_medic"
        int id_appointment_state FK "id_appointment_state"
    }

    appointment_item {
        decimal value
        int key PK "id_appointment_item"
        int id_appointment FK "id_appointment"
        int id_service FK "id_service"
    }

    consultation {
        datetime date
        decimal value
        int key PK "id_consultation"
        int id_client FK "id_client"
        int id_medic FK "id_medic"
        int id_appointment FK "id_appointment"
    }

    consultation_item {
        decimal value
        int key PK "id_consultation_item"
        int id_consultation FK "id_consultation"
        int id_service FK "id_service"
    }

    client ||--o{ appointment : books
    client ||--o{ consultation : undergoes
    medic ||--o{ appointment : schedules
    medic ||--o{ consultation : conducts
    appointment }o--|| appointment_state : has_status
    appointment ||--o{ appointment_item : includes
    appointment_item }o--|| service : refers_to
    appointment ||--o{ consultation : results_in
    consultation ||--o{ consultation_item : includes
    consultation_item }o--|| service : refers_to

Description #

• The property “name” in the table “appointment_state” can have the values: “Pending”, “Processed”, “Canceled”.

• In entity “appointment_state”, name represents the state that will be displayed on the screen, label represents the value that programmer will use to filter the states, and the uuid represents a value to distinguish the states.

• An appointment/consultation can have multiple services, a medic and a client.

• OBS: The value of the field “uuid” is generated automatically.

Tasks #

• client/medic/service/appointment_state/appointment/appointment_item/consultation/consultation_item: Add, Edit, Delete.

  • When the appointment is created, it will automatically become “Pending”.
  • The field “Appointment state” from entity “appointment” will be read-only in edit view.

• Create a catalog for entities: “appointment_state”, “appointment”, “medic”, “client” and “service”.

• Check appointment record availability (filtering appointments).

• Create event listeners for tables that needs validations (e.g., adding a client without a name, phone number, etc., should not be allowed).

• In appointment and consultation, calculate the total value of services:

  • Add a new search view called “Cost of Services”.

• Add a new entity action at the appointment level “Cancel appointment”:

  • It will change the appointment status to “Canceled”;
  • An appointment cannot be canceled if it is in the “Processed” state → validation message;
  • An appointment cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the medic level “Generate consultation”:

  • Parameters: client, service.

    • If one or both of the parameters are empty → validation message.

• Add a new entity action at the appointment level “Generate consultation”:

  • First check if a consultation has already been generated from the same appointment;
  • After creating the consultation, the appointment will have the “Processed” state;
  • It can’t be generated if it is “Canceled” → validation message.

• Client report: consultation situation at client level (total number of consultations, service value).

• Medic report: consultation situation at medic level (total number of consultations, service value).

• Service report: consultation situation grouped by services (total number of consultations grouped by services, value).