Keep a foreign key NULL if info is easily determine?

I have the following tables:

TABLE country
(
    country_id int not null,
    name varchar(32) not null,
    primary key (country_id),
    unique key (name)
);

TABLE place
(
    place_id int not null,
    name varchar(32) not null,
    country_id int not null,
    primary key (place_id),
    unique key (country_id, name)
);

TABLE image
(
    image_id int not null,
    title varchar(75) not null,
    caption varchar(500) not null,
    filename varchar(29) not null,
    country_id int,
    place_id int,
    primary key (image_id),
    unique key (filename)
);

An image can have a country (or not) or a place (or not).

Let’s say that I have a record in the country table:

23 | Spain |

And places in the place table:

3 | Madrid | 23
6 | Barcelona | 23

I want to tag an image with the country Spain and a place Barcelona

In the image table, should I enter the country_id and the place_id or just the place_id since the country is already determine by the place_id?

Answer

Change your mental focus. Focus on a “place” being associated with an image. Then make places work for both both “Spain” and “Madrid, Spain”. Do not attempt to jump past places to get to countries.

So, in places, allow an empty name with a non-empty country to indicate “Spain”.

I would use the standard 2-letter country codes instead of a 4-byte INT. Then I would either display “ES” with the images for Spain or have a lookup table like your current countries.