I'm finding it a little difficult to follow your design without some sort of entity diagram, but I think you might have the relationship between Costs
and the associated Products
and Services
backwards. You are structuring it such that a costs either has a product or a service. This creates a 'checkerboard' table design where every row contains at least one null and it's something I would avoid. I'm not certain but I think you can always avoid this kind of non-normalized approach in a relational model.
If you instead put a CostID
FK on both the Products
and Services
, you eliminate the problem and you can enforce a non-null constraint on both. In this scenario, the relationship seems more natural i.e.: I would tend to think of products and services as having costs, not the other way around. This isn't always the case, though. Sometimes eliminated nulls with this kind of approach may not seem completely intuitive. For example, we tend to talk about a shipping container having items, but in a good relational model, the items have a container (which they share with other items.)
In addition to the main question, there's an unclear relationship between Services
and Projects
. The reason I mention it is that you have a ProjectID
on Products
and Costs
but you don't mention one for Services
. Having a redundant FK as with Costs
and Products
can present a risk for inconsistent data. I would recommend either putting the ProjectID
on Costs
or put it on Serivces
& Products
but not both.