Even 2 is overblown.
You have hierarchical data, because any one city can only be in one state, which can only be in one country.
Therefore the city table only needs a foreign key to state, thereby automatically making the country information available. This elegantly and correctly handles the two towns called Columbus, the two towns called Washington, one of which is in England, and the other in that strange land 3,500 miles off England's West coast, er, I think it's called America.
I would also recommend losing the "tbl" prefix from the table names, and the needless abbreviations. Everything in a database from which one selects data, can be reasonably assumed to be a table, unless specified otherwise, e.g. V_COUNTRY would be a view about countries.
You also need to constrain your data to be valid, e.g. no null country, state or town names. No leading or trailing white spaces, and initial capitals for all names (because a country is a proper noun).
As an example, try something like
Code:
CREATE TABLE COUNTRY
(
COUNTRY_ID INTEGER,
COUNTRY_NAME VARCHAR2(100)
);
ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY PRIMARY KEY (COUNTRY_ID);
ALTER TABLE COUNTRY ADD CONSTRAINT NN_COUNTRY_NAME CHECK (COUNTRY_NAME IS NOT NULL);
ALTER TABLE COUNTRY ADD CONSTRAINT UQ_COUNTRY_NAME UNIQUE(COUNTRY_NAME);
ALTER TABLE COUNTRY ADD CONSTRAINT PROPER_NOUN_COUNTRY_NAMES CHECK (COUNTRY_NAME = INITCAP(COUNTRY_NAME));
CREATE TABLE STATE
(
STATE_ID INTEGER,
STATE_NAME VARCHAR2(100),
COUNTRY_ID INTEGER
);
ALTER TABLE STATE ADD CONSTRAINT PK_STATE PRIMARY KEY (STATE_ID);
ALTER TABLE STATE ADD CONSTRAINT NN_STATE_NAME CHECK (STATE_NAME IS NOT NULL);
ALTER TABLE STATE ADD CONSTRAINT PROPER_NOUN_STATE_NAMES CHECK (STATE_NAME = INITCAP(STATE_NAME));
ALTER TABLE STATE ADD CONSTRAINT STATE_MUST_BE_IN_A_COUNTRY CHECK (COUNTRY_ID IS NOT NULL);
ALTER TABLE STATE ADD CONSTRAINT FK_STATE_COUNTRY FOREIGN KEY (COUNTRY_ID)
REFERENCES COUNTRY(COUNTRY_ID);
Resist the temptation to use the field name of "NAME" instead of COUNTRY_NAME or STATE_NAME as the word NAME on its own is a key or reserved word in Oracle (where I did the above example). For this reason I suggest you use a colour context sensitive editor to do your create table statements, so that you automatically are made aware of any such words.
I suspect that you are using Access, in which case this may well be irrelevant, as I believe it provides a designer form for the purpose.
Regards
T
