CODEWITHSUNDEEP
mysqlsqldatabase-designforeign-keys
Marco
Marco

Reputation: 2737

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?

Upvotes: 0

Views: 47

Answers (2)

Rick James
Rick James

Reputation: 142218

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.

Upvotes: 1

GMB
GMB

Reputation: 222432

Polymorphic relationships are not easy to model in a relational database.

Here, I would suggest another approach that what you have set up: build two different bridge tables for images - one for countries, and the other for places.

That would look like:

create table countries (
    country_id int primary key,
    ...
);

create table places (
    place_id int primary key,
    ...
);

create table images (
    image_id int primary key,
    ...
);

create table image_countries (
    image_id   int references images(image_id),
    country_id int references countries(country_id),
    primary key (image_id, country_id)
);

create table image_places (
    image_id   int references images(image_id),
    place_id   int references places(place_id),
    primary key (image_id, place_id)
);

You are then free to link a given image to as many places and countries as you like. If you want, instead, to allow just one country and one place per image, you can change the primary key of each bridge table to just image_id.

Upvotes: 2

Related Questions