Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    referential integrity?

    I have COMPANY, BANK, CUSTOMER tables which contain state, city columns. I have two options right now for the state and city column.

    Option 1: just put value in those tables;

    Option 2: create a CITY and STATE table to hold value for city and state and have COMPANY, BANK, CUSTOMER tables a reference to CITY and STATE table to get the value. For example the STATE table may has two columns: stateName and countryCode, the CITY table may has two columns: cityName and stateName. In BANK, COMPANY, CUSTOMER tables, the stateName and cityName will be there and they will be a foreign key to CITY and STATE table.

    sounds option 2 is better but it will create unnecessary join and downgrade the performance?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "better" based on what criteria?

    i kind of like option 1 because you have given no evidence that there is a need for option 2

    here's an interesting comparison --

    suppose you had a table of employees, and each employee has a firstname and a lastname

    would you create a firstname table and link all the firstnames to it via relational integrity? would you create a lastname table and link all the lastnames to it via relational integrity? if not, why not?

    what makes the firstname/lastname relational integrity question any different from cities and states?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If you are going to store additional information about each city and/or State, such as demographics, or date of last survey, or whatever, then these represent distinct informational entities and should be split out into separate tables.
    If you want to ensure consistent spelling of city or State names across the database, to prevent people from entering "Minnesota" and "Minesota", or both "St Paul" and "St. Paul", or you want to prevent people from entering "Indianapolis, Nebraska", then you need to split these out into separate tables to ensure data integrity.
    Otherwise, leave them as attributes of the Custom, Bank, or Company.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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