This might force a bit of change for your tables, but we do something like this at work:
Ensure each table has a int/guid primary key of some sort:
Organization Table
ID | Name
1 | MyOrganization
Then create a type-table to house your different LeadSource types:
LeadSourceTypeKey | Description
1 | Organization
2 | Company Office
Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType
Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:
SELECT *
FROM [ProductSoldList] PL
INNER JOIN [Organization] O
ON PL.LeadSourceID = O.ID AND
PL.LeadSourceType = 1
INNER JOIN [Company Office] CO
ON PL.LeadSourceID = CO.ID AND
PL.LeadSourceType = 2
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.
Best Answer
These rules imply that each employee has a role that requires they work either only for a bureau, an office, or a division, or requires they work for a given bureau and many offices, or an office and many divisions, or a division, and that only employees who work for a division but are not division staff run projects. The schema shown however asserts only that employees work for a division in any given role and that any of them can run a project. Changes are needed to bring the schema into line with rules.
The simplest way to address the business rules is to add tables to associate employees to offices and bureaus in addition to divisions. Each employee would then be associated to each organization level in which they work. Employees working only at a bureau, or office, or division would only be associated to that level. Employees working at a bureau who also work at many offices would be associated to the bureau in that associative table and then also associated to the many offices by that associative table. Note this approach only addresses where each employee actually works and not the rules as to which roles can work at which organizational levels. To address the rules more discovery and modeling is required and is beyond the scope of this answer.
Secondly, the business rules imply that an employee has a single role - say a CIO or office manager or division chief - and not a role per associated location. The schema can be brought into agreement with this rule, if correct, by making the role table a parent of the employee table such that each employee is assigned a single role. Now this implication may or may not be true. Perhaps an employee can have only a single role at a moment in time, but many over time. In this case temporal columns are also required. Perhaps an employee really can have many roles at the same time at different organization levels, in which case the role would not be determined by the employee. Ultimately, more clarity is needed on the full scope of the business rules by having the subject matter expert review the model and determine if it represents accurately the rules by which they operate. It is vital to fully understand and document the functional dependencies and natural keys in order to arrive at the correct logical design. Right now the diagram does not show any natural keys so even if the functional dependencies were presented it would not be possible to determine if the logical design is in agreement with them. Finally, the diagram shows that each parent entity is optional and I doubt that is really the case. A business SME can determine under what circumstances the parent occurrence is optional, and for each of those circumstances more work is needed to determine why the parent occurrence is optional and resolve it so it is no longer optional.
I hope these points help in improving the design to meet all the informational objectives.