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:
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.
(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.
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...
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.