Narrative description of the "medical practice" database assignment:

Design a database for a small medical practice with several physicians. The purpose of the database is to support the administrative functions such as billing, scheduling and patient tracking. The medical practice has multiple offices; hence, physicians may be scheduled to be at different and/or multiple locations. However, each physician has be assign a primary location.

Any patient may see any physician, and, over time, a patient may see different physicians. A patient may complain about multiple symptoms (or ailments) during a single appointment. A unique ailment should only be listed once in a single appointment. However, a single ailment (e.g. bronchitis) can span multiple appointments. In addition, a patient may see only one physician per appointment.

Since the medical practice prefers to use a standard set of terminology/phraseology for describing all the aliments, a table should be created in the database to keep track of all the possible ailments and their associated attributes (e.g. description of the ailment, recommended treatment, charges for that treatment and so on). During an appointment, the attending physician can treat multiple ailments.

Your solution should include separate tables for patients, physicians, appointments and ailments. For each table, analyze what should be the appropriate and necessary fields in order to satisfy the purpose of the database design. Points will be deducted when essential fields are missing from the table. Point will also be deducted when unnecessary necessary fields are included, e.g. if you have foreign keys in a table that are not linked, then it is unnecessary. The solution that the professor is looking for has a pure junction table-one that is used to depict a many-to-many relationship from two one-to-many relationships. A primary key with multiple fields is needed in the pure junction table to ensure that there are no duplicate diagnostic codes for the same appointment.

The final results for the assignment include a database that contains only the required tables (i.e. 5 of them) and a data model shown in the form of a Relationships Diagram. Be sure that all the fields are appropriately typed and their properties configured/constrained. Do not enter any data into the tables.

Recommended steps to following:

1. Start a new database from the Blank database template in Access 2013, i.e. do not start from any other templates or existing databases.

2. You are to initially create the following 4 tables:

• Patients

• Physicians

• Appointments

• Ailments

3. General notes regarding database design

• Decide what fields are appropriate for which tables.

• Keep in mind the purpose of the database design, i.e. to support the administrative functions such as billing, scheduling and patient tracking for the medical practice. It would be helpful for you to search for open source applications for these functions to serve as a reference on what fields are necessary.

• Always work in the Design View when you are assignment field names and field properties, i.e. do not work in the Datasheet View.

• Do NOT enter data into the tables, i.e. just define the field properties of the tables.

4. Patient table design

• What kind of patient information does the medical practice need in order to do assure that it gets paid? For example: contact information, employment information, insurance information, etc.

• For each piece of information that is needed in the database design, it should become a field that you need to specify.

• It is important to have the appropriate level of granularity in the design of the fields. For example, a patient's name is not just one field, it should be at least first name, last name, etc. Likewise, an address is not just one field, but it should be separated into street, city, state, zip, etc.

• For each field, be sure to assign the appropriate Data Type to ensure data integrity and the support of downstream manipulation/extraction of the data.

• Also for each field, be sure to assign the appropriate Field Properties to constraint the values it should hold and to facilitate formatting of the content.

• What is the unique identifier for a patient? Assign that field to be the Primary Key for this table.

5. Repeat the process in 4 for the remaining 3 tables

• Sample design considerations for the Physician table: what kind of physician information would an insurance company want when it process a claim for reimbursement? What kind of physician information would the other stakeholders (e.g. the patient and the medical practice itself) like to have on the doctors?

• There is no need to create tables for office locations and insurance companies.

• Sample design considerations for the Appointments table: who is scheduled to meet? When? Where? Why?

• Sample design considerations for the Ailments table: what is the problem? Is it a symptom, an ailment or pathology? Be clear about the difference between the values that a field holds (i.e. the content) versus the field itself (i.e. the container). For example, the field "ailment description" can hold explanatory value such as "migraine headache", "torn right meniscus", "subdural hematoma", etc. Those descriptive values should not be individual fields. BTW, there are more hints about the fields of the Ailment table in the narrative description section.

6. Prepare the Relationship canvas

• Open the Relationship canvas by clicking the Relationship button on the Database Tool tab.

• Populate it with the 4 tables by right-clicking on the blank Relationship canvas and select the desired option in the context menu.

• Enlarge the window for each table so that most (if not all) the fields are visible without scrolling.

• Position the tables so that they do not obscure each other.

• Common mistakes to avoid:

a) If Access complains that someone else is working on the table(s) or relationship when you attempt to make modifications, it is because you have more than one associated object opened with modification(s). Hence, the best remedy in this situation is to Save and/or Close those other objects before you continue.

b) If Access complains when you try to link two fields together, a frequent mistake is that the two fields are of different Data Types, e.g. a Number field cannot be linked to a Text field. BTW, an AutoNumber field can be linked to a Number field. However, you should never link two AutoNumber fields together.

c) If Access does not let you assign a field to be the primary key, the cause may be that you have populated the table with records and the in the values in that field are not unique. Hence, by definition, that field cannot be the primary key. The best remedy in this situation is to delete all the records in the table.

7. Define relationships

• Recall that you need to associate two one-to-many relationships in order to depict a many-to-many relationship.

• There is a many-to-many relationship between patients and physicians.

• The Appointment table brings together the Patients table and the Physicians table.

• There is a many-to-many relationship between (a patient's) appointment and his/her ailments.

• A 5th table is needed and it serves the following purposes:

a) The 5th table brings together the Appointments table and Ailments table.

b) Since the 5th table is a pure junction table, the only two fields in it are the primary keys of the Appointments table and Ailments table.

c) The 5th table ensures that each unique ailment should only be listed once per appointment. You do this by creating a key that is a combination of the two fields in it. Highlight the two fields, then right-click and select the desired option in the context menu. If done
properly, you should see a key symbol by each of the two fields.

• There is only one relationship between each set of tables.

• At least one side of each relationship is a primary key.

• There should be no foreign keys in any of the tables which are not use to link to another table.

• Open up the tables so that most (if not all) the fields are visible without scrolling.

• Do not cross your relationships in the Relationships Diagram.

• Show the relationship type (e.g. one-one, one-to many, etc.) by right-clicking on a relationship line and check the Enforce Referential Integrity checkbox.

8. Create a report

• When you are all done with the above 7 steps and are satisfied with your database design, then create a report from the Relationship Diagram. There is a button in the Design tab titled Relationship Report that automatically creates a report from the Relationship Diagram.

• This report is a static image snap shot of the Relationship Diagram. Hence, if you make subsequent changes to the database and/or the Relationship Diagram, changes will not be reflected in the report automatically. Therefore, create the report toward the end of the assignment when everything is finalized. Otherwise, you will need to re-snap an image from the Relationship Diagram, and redo all
the embellishments for the report.

• Open up the report in Design View.

• Add in the 3 pieces of identifying information (described at the top of page 1 of this document) on the report.

• Apply some rendering features to embellish the report so that it is more appealing.

• Save your report and give it a name

9. Create a macro to launch (i.e. OpenReport) the report by the name. As an additional challenge, you can add an additional action to the macro so that the first action of the macro is to open a form that contains the 3 pieces of identifying information and the second action of the macro is to open the report with image of the Relationship Diagram.

10. In order for this macro to be automatically invoked by Access at the opening of your database, the macro needs to have a certain reserved name. Go search the internet to find out what that name is and named your macro accordingly.