Attaching data at differnet levels in a heirchy

[ormico]

I don't think this is too complex, but I can't figure out what what the correct solution is.

I have 6 tables: Customer, Project, Rate, Hours, EquipmentType, Equipment

All projects belong to a customer, so customers have a PK CustomerId and Project has a PK {CustomerId, ProjectId} where CustomerId is a FK to Customer.

A rate can be defined as being at a project level or a Customer level. A project must specify if it is using the customer level rates or its own rates.

So what I wanted to do at first was creat the key in Rate as {CustomerId, ProjectId NULLABLE, EquipmentTypeId}. The idea being that a NULL ProjectId would mean that the Rate was Customer level since it didn't point to a project.

But of course that won't work. In addition to SQL Server not allowing a PK with a NULLABLE it would also mess up my relationship between Rate and Hours.

I wanted to specify in Hours the Rate, Project (and Customer), and Equipment and Equipment type.

I didn't want to create 2 rate tables, one for Customer level rates and one for Project level rates, but is that what I should have done?

I hope this is a clear explanation.

Does anyone have a better idea how to create what I want.

[iburyak]

It is hard to give you advice without seeing any data.
But I don't think Project table should have CustormerID at all.
Usually people create Customer table with CustormerID PK and
Project table with ProjectID PK and in between for multi-to-multi relationships you should have CustomerProgect table with CustomerID, ProjectID PK and CustomerID FK and ProjectID FK.
The same is with a Rate. Just create Rate table with column RateType where you can store “C” or “P” meaning Customer or Project type rate.
And in between just create Customer_Rate table with CustormerID, RateID PK and corresponding foreign keys.

Hope it helps.