locked
3NF with composite keys RRS feed

  • Question


  • We are receiving two feeds for our reporting database.
    Feed1 contains the attributes of a City. (primary key consists of CountryCode, StateCode and CityCode)
    Feed2 contains the daily transactions of the City (primary key consists of CountryCode, StateCode, CityCode and Date)

    What is the best design to keep the data in 3NF form?

    Option 1:

    Create "City" table to hold city details and "City_transaction" table to hold daily transaction details.

    City: CountryCode, StateCode, CityCode,<Other Attributes>

    CityTransaction: CountryCode, StateCode, CityCode, Date, <Purchase and Sales amounts>

    OR

    Option 2:

    Create "City" table and add CityKey to represent the composite keys CountryCode, StateCode and CityCode and then create "City_transaction" table with CityKey replacing the composite keys (as mentioned below).

    City: CityKey, CountryCode, StateCode, CityCode <Other Attributes>

    CityTransaction: CityKey, Date, <Purchase and Sales amounts>

    Thursday, May 1, 2014 1:40 PM

Answers

  • There is no right answer, though I'm sure you will get some strong opinions about NEVER using composite keys.  I personally would lean towards creating an artificial key for "location" (a more generic term than "city") and use that for your foreign key (while also implementing the proper unique constraint for the natural key).  However, I suggest you try it both ways and determine the benefits and costs yourself.  Before you make any decisions, you REALLY need to understand how your "feeds" work and how the information they provide can change over time.  This can have a significant impact on your decision.  You also need to determine how the data is used.  If you frequently find that transaction queries need the location information (which is quite believable) then there may be an advantage to using the composite key. 
    • Marked as answer by 32988 Friday, May 2, 2014 2:02 PM
    Thursday, May 1, 2014 2:20 PM
  • I prefer to design with SURROGATE INT PRIMARY KEY (option 2).

    I would make the composite key (CountryCode, StateCode, CityCode ) a UNIQUE KEY.

    Based on query requirement, make the PK or UQ clustered.



    Kalman Toth Database & OLAP Architect T-SQL Scripts at sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by 32988 Friday, May 2, 2014 2:02 PM
    Thursday, May 1, 2014 2:21 PM

All replies

  • There is no right answer, though I'm sure you will get some strong opinions about NEVER using composite keys.  I personally would lean towards creating an artificial key for "location" (a more generic term than "city") and use that for your foreign key (while also implementing the proper unique constraint for the natural key).  However, I suggest you try it both ways and determine the benefits and costs yourself.  Before you make any decisions, you REALLY need to understand how your "feeds" work and how the information they provide can change over time.  This can have a significant impact on your decision.  You also need to determine how the data is used.  If you frequently find that transaction queries need the location information (which is quite believable) then there may be an advantage to using the composite key. 
    • Marked as answer by 32988 Friday, May 2, 2014 2:02 PM
    Thursday, May 1, 2014 2:20 PM
  • I prefer to design with SURROGATE INT PRIMARY KEY (option 2).

    I would make the composite key (CountryCode, StateCode, CityCode ) a UNIQUE KEY.

    Based on query requirement, make the PK or UQ clustered.



    Kalman Toth Database & OLAP Architect T-SQL Scripts at sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    • Marked as answer by 32988 Friday, May 2, 2014 2:02 PM
    Thursday, May 1, 2014 2:21 PM