5

I'm working on a database that stores address information. (Related to this question I posted)

I have 2 tables:

CREATE TABLE [dbo].[Country] (
[Code]          CHAR (2)     NOT NULL,
[Code3]         CHAR (3)     NOT NULL,
[CodeNumeric]   CHAR (3)     NOT NULL,
[Name]          VARCHAR (50) NOT NULL,
[ContinentCode] CHAR (2)     NOT NULL,
[CurrencyCode]  CHAR (3)     NOT NULL,
PRIMARY KEY CLUSTERED ([Code] ASC),
CONSTRAINT [CountryContinentFK] FOREIGN KEY ([ContinentCode]) REFERENCES [dbo].[Continent] ([Code]),
CONSTRAINT [CountryCurrencyFK] FOREIGN KEY ([CurrencyCode]) REFERENCES [dbo].[Currency] ([Code])
);

and

CREATE TABLE [dbo].[City] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Code]        VARCHAR (10)  NOT NULL,
[Name]        NVARCHAR (50) NOT NULL,
[CountryCode] CHAR (2)      NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [CityCountryFK] FOREIGN KEY ([CountryCode]) REFERENCES [dbo].[Country] ([Code])
);

Now I want to add a Capital to my Country table using a FK from the City table. I know that this is impossible because of the constraints (there has to be a City with the Capital's Id, but there also has to be a Country with the Country's Code for the City...if this makes any sense).

Approach 1: I'm using this database together with C# and was thinking to write method that would first add a Country with a Capital that is nullable, then add the City to its table and update the Countries Capital with the City that was added.

Approach 2: Another approach I consider is not adding Capital to the Country table but instead creating a new table CountryCapital and link both the Country and the City (Capital) together.

Which one of these 2 is the better approach? What I think is that approach 1 is a good solution if the database is maintained through C# but would be hard to keep up with if data is added manually. Approach 2 on the other hand looks easier to maintain if manual edits have to be made (add a City, add a Country and add a record in the CountryCapital table with the City's Id and the Country's Code).

  • 1
    I suggest approach 2 - where there will be a unique constraint on the CountryID of that table and only one FK, with CountryCapital (CountryID, CityID) referencing City (CountryID, CityID). For more details, see: How to have a one-to-many relationship with a privileged child? (Parent=Country, Child=City, FavouriteChild = Capital). There's also another solution described there, using a BIT column. – ypercubeᵀᴹ Sep 18 '15 at 13:13
  • An alternative might be to add an indicator attribute to City that would signify that a particular city is the capital of its respective country. – mustaccio Sep 18 '15 at 13:30
  • @mustaccio I've also played with that idea, but that would add a lot of "useless" info to that table. If there are for example 20 countries in the table (20 capitals), and over 1000 cities, then the column is only useful for 1/50 cases. – Krowi Sep 18 '15 at 17:33
  • @ypercube: It's pretty much the same question if I'm correct? But why do you do CityID INT NOT NULL FOREIGN KEY (CountryID, CityID) REFERENCES Child (CountryID, CityID)? I would've added 2 FK's for CountryID and CityID and reference their tables respectively. Is there a difference? – Krowi Sep 18 '15 at 17:48
4

For just the country/capital relationship I would go with option 1 and allow the capital to be NULL for a country record, rather than introducing an extra table. In both cases you are allowing the capital to be undefined at any given time so there is nothing to chose between the options from the completeness and referential integrity points of view.

If you do go for option 2, if the country column is not your PK make sure it is set to be UNIQUE otherwise you could accidentally end up with two capitals. With an extra "relationship type" column you could offer extra flexibility with this table though, by allowing things like "second city" and "religious capital" (some countries have this separate from their political capital for various reasons) to be defined.

  • 1
    Countries with two/split capitals. I didn't think the list would be so long until I looked it up. – Erik Sep 18 '15 at 15:10
  • 1
    I wonder how many on that list are wrong? The Netherlands for example has a single capital city, but the seat of government is elsewhere. +1 for allowing this information to be represented in some way. – Colin 't Hart Sep 21 '15 at 8:53
4

I always look first from a data integrity point of view when considering table designs. One problem with adding a "is the capital" indicator to the Cities table may be space but that is trivial to the anomalous conditions this makes possible. What happens when more than one city in the same country is labeled 'C' (for Capital)? What does it mean if no cities in a country are labeled?

This would seem like a good time to use an intersection table to implement a one-to-many relationship.

create table CountryCity(
    CountryID  int not null,
    CityID     int not null,
    ...,
    constraint  PK_CountryCity primary key( CityID ),
    constraint  FK_CountryCity_City foreign key( CityID )
        references Cities( ID ),
    constraint  FK_CountryCity_Country foreign key( CountryID )
        references Countries( ID ),
    constraint  UQ_CountryCity unique( CountryID, CityID )
);

A city may only have one listing but a country can have many cities.

Notice that although CityID is the PK and therefore must be unique, we still created a unique constraint on (CountryID, CityID). This makes it possible to create a table like this:

create table CountryCapital(
    CountryID   int not null,
    CapitalID   int not null,
    constraint  PK_CountryCapital primary key( CountryID ),
    constraint  FK_CountryCapital_CountryCity foreign key( CountryID, CapitalID )
        references CityCountry( CountryID, CityID )
);

CountryID is the unique key field here and the capital city defined here must already be defined as a city of that country. Once that is done, no other city of that country can by similarly defined. So we can ever have only one city defined as the capital of any country and that city must already be defined as a city belonging to that country.

Of course it is still possible to not define any city as the capital of a country, but that is a case of missing data rather than anomalous data.

Sure, you have more tables to work with and this makes the queries a little more complicated. But think of the reduction in maintenance effort going forward knowing you never have to worry about things like multiple cities designated as the capital of the same country or a city designated as the capital of a country it doesn't even belong to. It's all trade-offs.

  • 1
    Thank you for your answer. Right now I applied the approach given by ypercube's comment (dba.stackexchange.com/questions/34151/…). What benefit does your approach have by extracting the CountryCode from the City table and adding a 2nd table to link a country and a city? (New to database design.) – Krowi Sep 21 '15 at 8:57
  • The main benefit is that it is impossible to designate more than one city as the capital of a country. As you gain experience with databases, you will find Murphy's Law is right at home there: if something is possible, it will happen. So you have to write trigger and/or sp code to constantly check to see if a city is being made the capital of a country and if so that there is no other city already so designated. The best you can hope for, btw, is to reduce the probability, you can never entirely eliminate it. So you also have to put in place a procedure for correcting it when it happens. – TommCatt Sep 21 '15 at 19:15
  • @Krowi which suggestion did you follow? In my answer there, I have several, the first one being identical to this answer. – ypercubeᵀᴹ Oct 18 '16 at 15:31
0

Option 3: You can create an extra column in the City table instead as IsCapital and where ever its true, that's the capital of the county its related to.

  • I've also played with that idea, but that would add a lot of "useless" info to that table. If there are for example 20 countries in the table (20 capitals), and over 1000 cities, then the column is only useful for 1/50 cases. – Krowi Sep 18 '15 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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