Design and create a ERD diagram for a database for Doctors Office

1. One patient can see many doctors in the same office on the same day.
2. One doctor can also see many patients. On the same day.
3. If one doctor sees the same patient multiple times in one day, then its only recorded once in the database.
4. For each patient we are recording primary and secondary phone numbers.
5. A doctor can also be a patient.
6. A doctor cannot be his/her own patient.
7. Each patient will get 0 to 10 prescriptions(medicine).
8. Each patient can be given 0-5 tests (medical test).
9. A patient cannot be given a new prescription, test without first visiting the doctor.
10. Make sure that the Doctor ID is first 2 letter of his first name followed by number. Eg. If the Doctors name is Ron Gates, then his DoctorID can be RO3283.
11. Patient ID is a system generated number.
12. Data retrieval speed is a priority. Use techniques to make it faster.

The list of all the attributes are -

DoctorOffice

Patient Id

Name

Patient Address (ex of address 1234, street, city, state, Zip)

Patient primary phone number

Patient secondary number

Patient date of birth

Doctor Name

Visit date

Doctor phone number

Doctor Specialty

Prescription 1, Prescription 2 .. Prescription 10

Test 1, Test 2 .. Test 10

Doctor note ( 300 char length)