Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    14

    Is This Database Table Normalised???

    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

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    (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?

  3. #3
    Join Date
    Mar 2009
    Posts
    14
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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).

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Like what he said!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  7. #7
    Join Date
    Mar 2009
    Posts
    14
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •