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