Assiatance with SQL table relationship

Question

I'm fairly new to database design. Starting a new application that has four tables:

Staff
Vendors
Projects
ProjectSchedules

ProjectSchedules is a table where we will assign people to projects. Sometimes the people are staff, sometimes they are vendors. How can I have relationships for both staff and vendors in the ProjectSchedules table using one (or less?) columns?

It's important to note that vendors are usually a company (think staffing company).

I have done a similar thing in the past, but manually handled the non keyed relationships:

SELECT ISNULL(Vendor.Name, Staff.FullName) AS Staff 
    FROM ProjectStaffing INNER JOIN Vendors, Staff, etc.

Could you tell me if there is a better way?

possible duplicate of [Foreign key refering to primary keys across multiple tables?](http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables) — Drecker, Sep 12 '14 at 23:16
Check out the top answer to this question. I personally prefer the fourth suggestion that is offered (model an entity that acts as a base for your two tables) http://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables — Kik, Sep 12 '14 at 23:16

score 0 · Answer 1 · answered Sep 12 '14 at 23:25

One possible solution for this problem - define general term "Resource", so project uses resources instead of staff or vendors

Resource has id, name, type

depending on type you can have several tables with additional columns about resource (like first name, company name or server rack in case of hardware)

this approach some how better from scalability point of view, but sql queries can be a little bit weird if you try to display all resources assigned to project with full detailed information about each of them, but usually this can be solved at UI level

Another solution:

instead of one project schedules table you can have multiple relations:

project_staff
project_vendors
project_somethingelse

this approach somehow easier to implement, but it is harder to maintain later

Assiatance with SQL table relationship

1 Answers1