August 25, 2010 at 8:12 am
Hello,
I have a table of cities which include CityName, CountryCode (PK), ProvinceCode (PK). Initially I defined CityName as Primary Key, but now I am not sure if I would be better adding CityID (int) field as Primary Key.
I need your opinion on this one.
Thanks.
August 25, 2010 at 8:34 am
my table is similar, where we have an ID, cityname,state;, witht he PK being on the identity column ID, but with a unique constraint on cityname,state; at least in the US, a city name can exist only once in each state, but could exist multiple times in a country potentially(i.e Springfield,IL,OH,MA,NY, etc)
Lowell
August 25, 2010 at 8:46 am
It makes sense.
Thanks.
August 25, 2010 at 8:50 am
Therefore, I guess for Countries Table if I have just a column CountryName I can set it as Primary Key. I don't need to create a new field CountryID. Is it right?
August 25, 2010 at 9:16 am
dtopicdragovic (8/25/2010)
Therefore, I guess for Countries Table if I have just a column CountryName I can set it as Primary Key. I don't need to create a new field CountryID. Is it right?
it's your call, it can work either way;
at my shop, our foreign keys are always integers pointing to a PK identity; so our country table is CountryID/CountryName, with a unique constraint on countryname; it's just the way we like our data here.
so any child tables have that ID referencing it instead; having the FK for a varchar is no real issue...the index is a little bigger, but i can't say it's a bad thing, or gainst any common practices.
having the FK as the actual country name saves you a join or two to display the countryname witht he child data, i guess.
Lowell
August 25, 2010 at 9:20 am
Thanks for the input!
August 26, 2010 at 6:19 am
You might want to look at the well known ISO 3166 standard for country codes and for locations at the UNECE Locodes which you can find here
It is always a good idea to search for an industry standard prior applying anything like IDENTITY.
brgds
Philipp Post
brgds
Philipp Post
August 26, 2010 at 10:56 pm
It is better for you to add an ID. Because in the tables using country, the benefits are:
1. Space
2. Performance (numbers VS strings)
3. Bugs - "where CountryID = 24" is easier then "where Country = 'Bosnia Herzgovina'"
4. If country name changed, you will update it in 1 place
August 27, 2010 at 2:54 pm
There is a possibility of a lot of discussion being prompted here, but in my view (as in Philipps above) there is no good reason to use an "id" column on data such as countries.
There is a perfectly good, small, guaranteed unique, unlikely to change, easily verified standard in the ISO country codes so in this case I can see no good reason for inventing another arbitrary identifier.
The debate on whether identity is "ever" a good idea is much much broader, but the approach I always favour is that if there is a widely known (preferably ISO or industry standard) encoding for something then it should normnally be used, if no such standard exists then consider other options, but only after confirming the above.
Mike
August 27, 2010 at 4:05 pm
I still prefer the ID. I see the point that countries are standard, but in my lifetime, names have changed quite a bit. That's not a huge problem for updates, but it does mean that you need to touch lots of tables, potentially, when you could just update one table.
August 28, 2010 at 4:13 am
The two key elements to consider are size and stability. Ideally a primary key will not change and will use the smallest possible amount of space. This is why integer identity columns are so frequently used.
Size
Some of the benefits of using the smallest possible data types are:
Decreased database file size.
Increased index performance (fewer pages holding more rows).
Decreased usage of system resource (disk, memory, cpu, network).
While there may not be very many rows in either the city or country tables, consider that they are likely to be held in other tables as foreign keys where there may be many more records. They are also likely to used in reporting where you might want to analyse many rows by city or country, suddenly a few extra bytes soon becomes extra megabytes.
Stability
The reason a primary key requires stability (that is it does not change) is that it can be referenced by many other tables in the form of foreign keys. If the primary key value changes each of these related tables must also be updated otherwise you will be left with orphaned rows (rows in a child table for which there is no matching parent row), and referential integrity will be lost. This is bad practice, a mistake could easily be made.
As per the above neither the city name or country name attributes (columns) are good candidates. This leaves you with a choice between an integer identity column or an externally supplied list such as the ISO 3166 as suggested above for countries.
The pros and cons of using the ISO 3166 for you primary key are as follows:
4 Bytes required for integer column.
2 Bytes required for ISO 3166 reference (char(2)).
Identity column will not change, you control it.
ISO 3166 is controlled by a third party, countries do change albeit infrequently (a quick google found 30 new countries since 1990).
The choice is entirely yours.
I would recommend reading Data Modeling Essential 3rd edition (no I don't get paid in any way if you but this book :-)), I find this book extremely helpful.
MCITP SQL Server 2005/2008 DBA/DBD
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply