3

Today we were discussing the following:

Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so on.

One of our programmers proposed that we should use those abbreviations (RJ, SP, MG, etc.) as PK on the States table that we are planning to add to a new project.

Extrapolating the use of our database, I countered his argument saying that if we were to - someday - expand our services to other countries, we would have a problem with repeated abbreviations, for example: in the USA there is SC for South Carolina and on Brazil we've got SC for Santa Catarina; the same happens for MT, PA and MA. Counting on this we've agreed that there should be a ID column as PK IDENTITY.

Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea. Is it? Why? In which cases this could be applied? Should memory be considered in order to choose from one to another?

1
  • Just FYI, I added an UPDATE section to my answer to address the concept of starting out with just the "States" for Brazil, but then needing to expand to other countries later, all without too much effort ;-). Commented Jan 8, 2016 at 17:08

3 Answers 3

8

In most cases I agree with @Aaron's approach, but this just happens to be one of the few instances of there being a truly natural key to use: ISO codes (ISO 3166 specifically). For those who are unfamiliar with ISO, they are, in their own words (from the main http://www.iso.org/ page):

We're ISO, the International Organization for Standardization. We develop and publish International Standards.

ISO 3166-1 describes the country codes. While there are a few choices of codes to use (2 character, 3 character, and numeric), the 2 character codes are the recommended choice and the most widely used (including for most country-based top-level domain names).

ISO 3166-2 describes each country's subdivisions (e.g. states). This standard is divided into country-based sections (e.g. ISO 3166-2:BR for Brazil) and the codes are 1, 2, or 3 alphanumeric characters.

So, you could do something like:

CREATE TABLE dbo.Country
(
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      PRIMARY KEY,
  CountryName VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.CountrySubdvision
(
  CountrySubdvisionCode VARCHAR(3) NOT NULL
                                   COLLATE Latin1_General_100_BIN2,
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      CONSTRAINT [FK_CountrySubdvision_Country]
                                 FOREIGN KEY
                                 REFERENCES dbo.Country(CountryCode)
                                 ON UPDATE CASCADE,
  CountrySubdvisionName VARCHAR(50) NOT NULL,
  --  LocalizedSubdvisionName NVARCHAR(50) NOT NULL, -- ??
  CONSTRAINT [PK_CountrySubdvision]
             PRIMARY KEY (CountryCode, CountrySubdvisionCode)
);

The idea here is that you would place the CountryCode and CountrySubdvisionCode fields in any tables that needed the "state" value, and FK back to dbo.CountrySubdvision on (CountryCode, CountrySubdvisionCode).

While this method, using 3 - 5 bytes depending on the Country / Subdvision, is not as compact as using a 2-byte SMALLINT, it does have the advantage of placing human-readable / meaningful values in those related tables. This could easily reduce some number of JOINs (when you only need the 2-character code anyway) and can (at least slightly) reduce time spent debugging certain problems.

Please note that I specified a _BIN2 (i.e. binary) collation for both of the "Code" fields to assist performance. Even with an extra couple of bytes, that should be nearly as fast as comparing two SMALLINT values. The only downside there is that you need to be consistent in adding codes in all upper-case and people need to remember that they need to use all upper-case when filtering on these codes.

Please also note that I added the ON UPDATE CASCADE clause to the FK on dbo.CountrySubdvision to handle the off-chance that a CountryCode is changed by ISO (extremely unlikely). Along those same lines, the FKs created on tables where the CountryCode and CountrySubdvisionCode fields have been placed will also need to be set to ON UPDATE CASCADE. This will propagate changes made to CountryCode in dbo.Country to the tables that have both of the fields. This also will propagate any changes made to the CountrySubdvisionCode to those related tables. This is also highly unlikely to happen (especially for where most of our systems would be dealing with) though more likely than any changes being made to the CountryCode.


UPDATE

Now, supposing that we do not expand our services to other countries and stay only in Brazil I started considering the idea of using a VARCHAR(2) column as PK. In this scenario it doesn't sound like a totally bad idea.

Just to have this stated, if using this method (i.e. ISO codes) to denote "States" / "Provinces" and possibly Countries, then you do technically have the ability to start out handling only "States" for a single country. In this configuration you would simply remove the dbo.Country table and the CountryCode field (and associated FK) from dbo.CountrySubdvision. Then you would only have CountrySubdvisionCode to place in any related tables.

Now, if you later find that you need to expand the system to handle other countries, you can, at that time, do the following steps (none of which change any existing CountrySubdvisionCode values in any of the related tables):

  1. Create the dbo.Country table
  2. Populate the dbo.Country table
  3. Drop any FKs from related tables that reference dbo.CountrySubdvision
  4. Drop the PK on dbo.CountrySubdvision
  5. Add the CountryCode field to dbo.CountrySubdvision, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
  6. Recreate the PK on dbo.CountrySubdvision to be on (CountryCode, CountrySubdvisionCode)
  7. Create the FK on dbo.CountrySubdvision for the CountryCode field to reference the dbo.Country table
  8. Add the CountryCode field to all of the related tables that already have the CountrySubdvisionCode field, making it NOT NULL and with a DEFAULT of the CountryCode for the only country that you have been using so far (this DEFAULT can be removed at the end of this process, if you like)
  9. Recreate the FKs on those related tables for both fields to reference the dbo.CountrySubdvision table
  10. Remove the Default Constraints on the CountryCode fields from dbo.CountrySubdvision and the related tables
4

My choice would probably be as follows.

CREATE TABLE dbo.Countries
(
  CountryID tinyint PRIMARY KEY, 
  Name nvarchar(64) NOT NULL UNIQUE
);

CREATE TABLE dbo.States
(
  StateID smallint PRIMARY KEY,
  CountryID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.Countries(CountryID),
  Abbreviation char(2) NOT NULL,
  UNIQUE (CountryID, Abbreviation)
);

Notes:

  1. CountryID is a tinyint because there are only 196 countries in the world.
  2. StateID is a smallint because there are 196 countries * as many as 100 states/provinces. This column could potentially be replaced by a compound key (CountryID/Abbreviation), but since states are likely to be used in child tables that reference this one, a surrogate will simplify those references in other tables, as well as joins, and is often better (that is a generality based on my experience; there is no always).
  3. Abbreviation is a char(2) because a varchar that small doesn't make much sense, especially if all abbreviations will be exactly to characters. You might consider something slightly larger though (and also the full name as a separate column); I don't know all of the conventions around the world, but two characters might not be enough for some countries/languages. You might also need to use nchar if you plan to support any countries that may use Unicode characters in their state abbreviations (I don't know if this is possible; I doubt it, but you should check).
  4. I didn't name my constraints for brevity, but as a best practice, you should.

Note that an IDENTITY column alone would not really solve your problem. Sure you could tell SC with ID 4 from SC with ID 342, but those surrogates don't really tell a user anything - you still need another column to identify the country.

If you want to paint yourself into a corner and assume that you will never extend beyond Brazil, then your design is much simpler - in that case I would probably opt for the char(2) (again, not varchar(2)), since no other column would be needed for country and that would be a pretty small representation in child tables. But in reality I don't like painting myself in a corner. If there is any possibility that you could expand, you probably will, and changing that later will be more painful than you can probably imagine.

2
  • 3
    Australia uses three characters for state. The U.K. uses "county" as the sub-country part of the address, such as "Worcs" or "Oxon". New Zealand doesn't have states or any other equivalent. And then you have places like Japan which won't cope with char(2) either. But see how you go. :)
    – Rob Farley
    Commented Jan 5, 2016 at 7:48
  • 1
    Thanks @Rob, like I said, you should check. :-) Commented Jan 5, 2016 at 14:07
0

If you want to use a numeric use a smallInt(2 Bytes) NOT INT.

In my opinion you should have a small INT PK identity and the Abreviation should by a Column with UNIQue.

6
  • 1
    Unique on the abbreviation won't help when the OP adds other countries either. Possibly a more future compatible design would be a countries table and a states table - the former an identity and name, the latter CountryID and abbreviation (and the unique would be on both columns). Commented Jan 5, 2016 at 1:55
  • @AaronBertrand You are proposing a compound key? Commented Jan 5, 2016 at 2:15
  • 1
    @Human_AfterAll That is one option, sure. Is it the best for your scenario? I have absolutely no idea. Commented Jan 5, 2016 at 3:00
  • @AaronBertrand See, the real question could sum up as: Why using a varchar(2) instead of a smallint(2)? Why would one pick one and not the other? Is there a best pick? Commented Jan 5, 2016 at 3:45
  • 2
    @Human_AfterAll Well, for one, SQL Server doesn't have smallint(2), only smaillint, which supports 64K distinct values (-32,768 -> 32,767). varchar(2) would support 676 distinct values (26^2), assuming case insensitive and only A-Z. I don't know how you would define "best pick" - what is better, a tricycle or a Ferrari? You would make a predictable choice, now ask a 5-year old who wouldn't be able to drive the Ferrari. Also remember that you are not storing the same data in the columns represented by these two data types, so it's not a simple choice of one over the other. Other factors. Commented Jan 5, 2016 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.