Database "Size"
The database will have at least six basic categories/tables of data: Patients, Appointments, InsuranceCompanies, Staff, Procedures and Supplies. If you have any many-to-many (M:N) relationships in your initial database model, then you will probably have more than six tables in your final database model. In general, you will need to create a new bridge table to eliminate each of the M:N relationships in your initial model.
The number of relationships is not simply the number of conditions specified in the assignment. I specified eleven conditions:
- Patients must have at least one appointment, but they may have more.
- Every appointment must be for a single patient.
- Patients do not have to have insurance. If they do, then they may only have one insurance company. (Technically, this is not realistic... but it is the way we'll handle it for this assignment.)
- Insurance companies may represent one or more of the patients.
- Every appointment must have at least one staff member (e.g., doctor, lab technician).
- An appointment may use more than one staff member (e.g., a nurse or a medical assistant may help with some appointments).
- Staff members may work on any number of appointments, including zero (e.g., the receptionist and billing clerks never deal directly with the patients.)
- Every appointment must involve one or more procedures.
- Each possible procedure may be done on zero or more appointments.
- Appointments may use any quantity of each supply.
- Each supply may be used on zero or more appointments.
The first two conditions (#1-2) describe a single 1:N relationship between the Patient table and the Appointment table. Two conditions describe one relationship, not two different relationships.