Tables with FK's of each other

Question

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).

Answer 1

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.

Answer 2

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.

Answer 3

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.