Countries and capital cities

[smeco]

I want to have a table of cities and a table of countries.
For each city I need to say in which country it is, and for each country I need to say which one is the capital city.

How should I structure the tables?!

Should I have a 'CITIES' table with a field that indicates the country and a 'COUNTRIES' table which indicates the capital city, and somehow check that the capital city specified does actually belong to that country?!

thanks in advance
smeco

 

[Norie]

In your CITIES table add a Yes/No field, Capital say, that indicates if the city is a capital.

The COUNTRIES table should just have the one field for the country.