Relationship Question

[metrodub]

I am creating a database to track training for my company. We use outside facilitators to teach these classes. There are some instances where a facilitator (partner) works for more than one company (vendor). The second vendor is usually the partner working as a sole proprietor. My table structure for this relationship is pretty sound:

tblCompany
CompanyID (PK)
CompanyName
...more data

tblPartner
PartnerID (PK)
PFName
PLName
...more data

tblPartnerCompany
PartnerCompanyID (PK)
PartnerID (FK)
CompanyID (FK)

There is also a contract table, detailing pay rates, etc. As these rates are associated with the Vendor and not the Partner, a Partner who works for two Vendors will have different pay rates.

tblContracts
ContractID (PK)
...contract data
CompanyID (FK)

My question revolves around the Class table (tblClass). Because a Partner may work for more than one Vendor, there is a need to capture the CompanyID as well as the PartnerID when it comes to who taught the class. So, should tblClass be structured as such:

tblClass
ClassID (PK)
CourseID (FK) <---Course data
StartDate
EndDate
...more class data
CompanyID (FK)
PartnerID (FK)

And, if so, do I connect CompanyID from tblCompany or tblPartnerCompany? Same goes for PartnerID.

Thanks.

 

[johnherman]

From a purely relational modeling point of view, you would join to the bridge table (tblPartnerCompany). tblPartnerCompany will provide the links to tblCompany and tblPartner

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[BobRodes]

I don't agree that this is so (feel free to change my mind), and here's why. Partners and companies are not related to one another on account of classes, but on account of contractual agreements that they have with one another. As such, the fact that they are related is irrelevant to the handling of Classes.

Therefore, I would relate both the partners and companies tables directly to the Classes table. Since the classes don't care that partners and vendors are related, this is simpler and in the long run easier to maintain. For example, perhaps at some point the business rules would change to say that we are no longer keeping track of contractual relationships between vendors and teachers. We only care what vendor is billing us for a class and what teacher is teaching it. If you're involving the PartnerCompany table, you have to keep track of unnecessary and superfluous information to do the work.

IMO, the most direct approach is the one that best stands the test of time.

Bob