Hi,

I am designing a few new database tables. I seem to just having a mental blank as I can't figure out if part of the design is normalised or not. I just can't get my head around figuring it out. The design is as follows (a little simplified for examples sake)

Already existing in the system is a Customer table:

Customers (CustomerId:CustomerNumber:Name:etc....)

and a customer can have multiple contacts, although a contact can only belong to one customer:

CustomerContacts(CustomerId:ContactId:Name:etc....)

The new table is for PricingQuotes which will be for a customer. It can also be for a particular contact for the customer, or can have no contact specified.

I have the following table...

PricingQuotes(QuoteId:CustomerId:ContactId:CreateDatetatusCode:etc... .)

Where CustomerId is a foreign key to the customer table and CustomerId:ContactId is a foreign key to the CustomerContacts table. Finally ContactId can be NULL if no contact has been specified. Is this in 3NF? I think it's because the CustomerId and ContactID are a composite key which is the foreign key to the CustomerContacts table that is throwing me. I have some nagging suspicion that ContactId is somehow transiently dependent on CustomerId but for the life of me I can see how else it could be setup. Maybe the design is right and I'm just having a bit of a mental lapse so it would be great if someone could deny or confirm mu suspicions.

Cheers,

Steve

(CustomerId, ContactId) cannot be a candidate key if ContactId is nullable. There's no obvious reason why it should be nullable though. Why would you want a row in CustomerContacts for a customer who didn't have a contact?

Sorry, I may not have been clear enough. I meant ContactId can be null in the PricingQuotes table. As you mentioned it will never be null in the CustomerContacts table.

Therefore if a PricingQuote is just generally for a customer the ContactId field will be set to NULL. If the PricingQuote is for a specific contact with the customer then the ContactId field will have a value and the CustomerId:ContactId foreign key will be used to get the CustomerContacts data.

Dave - either you have read it wrong or I have. That isn't what he is saying.

Essentially a quote will be associated with a customer, and may be associated with a customer's contact (though not necessarily).

Like what he said!

If a contact can only have one customer then customerid is a non-key attribute of CustomerContacts - there should not be a composite key. The foreign key in PricingQuotes therefore references a super key.

I would say it is not normalised. I also wouldn't worry about it since your design does maintain integrity. I can't think offhand of a design that would satisfy all requirements.

Yes of course. I think the design of the CustomerContacts table was throwing me. If the ContactId is the candidate key/primary key for CustomerContacts then the CustomerId field should just act as a Foreign key to the customer table. (And also informally as a super key that doesn't need to be enforced as a unique contraint as this is intrinsic in the table)

If I refactor this table then the CustomerContacts should just have a primary key of ContactId. There will be a foreign key linking the CustomerId field in CustomerContacts to the Customers table.

In the PricingQuotes table though, what is to stop a contactId being entered which does not match the relevant CustomerId. I assume I create a Foreign key to CustomerContacts that includes both the ContactId and CustomerId (a super key). Does this follow good database design practises or is there a more preferred way to do this? Alternatively if you were to design this from scratch would you use this design or again is there a preferred way of representing this data in a relational model...

So:

Customers (CustomerId:CustomerNumber:Name:etc....)

CustomerContacts(ContactId:CustomerId:Name:etc....)
-> FK_CustomerContacts_Customers on CustomerId

PricingQuotes(QuoteId:CustomerId:ContactId:CreateDatetatusCode:etc... .)
-> FK_PricingQuotes_Customers on CustomerId
-> FK_PricingQuotes_CustomerContacts on ContactId:CustomerId

This leads me to a another slightly related issue I have seen.

The current system uses surrogate keys for almost all tables (except instances that are keyed by specific natural keys like ISO codes etc...)

Using an order example they seem to have used the following design:

Orders (OrderId:Field1:Field2:etc...)

and

OrderDetails (OrderId:OrderDetailId:ProductId:Field1:Field2:etc...)

is my understanding correct that they are misusing the surrogate keys. ie the OrderDetails table should only have a primary key of OrderDetailId? My assumption of a surrogate key is that they should never be part of a compound key? So the above table design SHOULD have had a primary key OrderDetailId and then a unique key on OrderId:ProductId to enforce the natural key/referential integrity? I just want to clarify as this design decision they made keeps throwing me when I see it.

Cheers,

Steve