Good. The essentials are there. A first rough list of entities is to take the nouns of the story - office, employee, project, clients, assignment, skill. Each table has a unique, sequential, meaningless key. Columns are given descriptive names. Here are the suggestions by entity.
office> The manager is an employee and should have a separate foreign key relationship with manager_employee_id. There is a many-to-many with employee. Avoid M-to-M relationships. In this case, an office has many employees. But an employee has one home office. I suggest naming fields uniquely with a common suffix describing the domain, office_address, instead of address.
employee> The job_id should be a foreign key to job instead of title. The office_id should be a foreign key to office instead of location. The column names should be hire_date instead of the start_date to avoid confusion with the project start_date. Use employee_address instead of address based on the domain suffix rule.
project> The project manager is a separate foreign key to employee pm_employee_id. What is the business difference between estimated_cost and quoted_cost? Make sure there is a foreign key field to match every relationship line. Client_id should be the foreign key. Office_id should be a foreign key.
assignment> Good, this is an intersecting table between project and employee. I suggest renaming the date to assigned_date to keep the suffix consistent. Notice that the hours_worked are in this table with the assigned_date. The assigned_date happens once. But the hours worked happen daily.
daily_assignment> Maybe there should be a separate child table called daily_assignment with its own primary key, a foreign key to assignment, and the worked_date and hours_worked as fields. This tracks each item of data uniquely at its own level.
client>
I suggest renaming the address to client_address.
There are ways to make it more complicated. But I think this model works well. It should give you flexibility to make business changes, yet still be simple enough to code.
Make a generic table call Users with a unique ID by line (user_id)and put your generic infos (email, password...), used for both Registered and Unregistered users.
Next, create a table "Registered_Users" to store specific info, with a column user_id (which point to Users table : foreign key).
If you want to query just global info about all your users, query :
SELECT ...
FROM Users u
If you want to query your specific info about registered users, query:
SELECT ...
FROM Users u
JOIN Registered_Users re
ON (u.user_id = re.user_id);
Max.
Best Answer
I would take the approach for diagram 1. When approaching people in a database, the patients are essentially objects. They don't log in, they only have data. A doctor (or any employee) would be able to log in as users, so would have fields in their table like a login_id, password, and such. A point of confusion may come up when you have a doctor that is also a patient, but then they would have an employee_id\user_id AND a separate patient_id.
As a side note, I would move the connection for medications to be attached to the treatment actions. That would associate a drug with a prescription event, which would be connected to a patient and doctor. Drug prescriptions are events in and of themselves, not necessarily a long-term association to just a patient.