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

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