Help with contract tracking relationships

[Sketchin]

Hey All,

I have a database that tracks projects and companies. I have been asked to add a table that will track contracts that are either associated with project OR with a company. I have made a very simple data model that is attached to this post that will hopefully help explain better. I should also mention that each project always has a company assigned to it.

My question is whether or not I am going about it the right way. Are there any obvious pitfalls with this relationship/table structure? I should mention that each relationship is 1 to many.

Thanks for any and all input and sorry for my rudimentary "data model"

 

[jdraw]

I have looked at your "model" and, although it was rudimentary and very high level, I thought it would be an interesting exercise to try and develop something that may be useful to you. I have made some assumptions and it has been a long while since I worked in this area, but I am attaching a jpg of the initial model, and an Access database with some tables and queries I used to vet/test the model.

You can use it or toss it as you please. It may give you some ideas, or it may just be confusing.

I hope it is helpful.

Good luck with your project.

 

[Sketchin]

Thanks for the help on this Jdraw. My only issue with it is that with the model this way, a company or project cannot have more than 1 contract.

I think what I would need to do is put CompanyID and ProjectID into ContractedEntity and remove CEID(FK) from company and project table.

Make sense?