[lmei007]

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

[r937]

"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?

[blindman]

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.