1

I have a table with machines data:

machines
----------
id
owner_id
// other data

We also have official machine data that comes from Germany and France. Both have the same information:

# Machine data (for german and french machines)
- machine_id: to tie to machines table
- record_number: unique number for each machine, different format for each country
- machine_name: the name of the machine
- market_id: id associated with the machine market, different format for each country

# Market data (also for german and french machines)
- market_id: machine market id, different format for each country
- market_name: name of the market
- specific country information that is different for France and Germany

At first I though that the relational schema would be like the following:

machines
---------
machine_id
owner_id
// other data

machine_information
---------
machine_id
record_number
country (German or France)
machine_name
market_id

france_markets
---------
id
market_name
// France specific column data

german_markets
---------
id
market_name
// German specific column data

But this can make it very difficult to filter data, because when getting a machine information, we do not know which market table we have to join.

I also though about splitting the machines_information into 2, one for each country

machines
---------
machine_id
owner_id
// other data

france_machine_information
---------
machine_id
record_number
machine_name
market_id

german_machine_information
---------
machine_id
record_number
machine_name
market_id

france_market
----------
id
market_name
// France specific column data

german_market
----------
id
market_name
// German specific column data

But this also has the same problem because when getting the machine information, we do not know which machine information table we have to join.

Is there a good solution for that kind of design?

Thank you!

1

1 Answer 1

0

This is called a polymorphic association or supertype/subtype, and is usually solved by using a base table and multiple child tables in a 1:1 relationship.

A well-known trick here is to add a Type column to the base table as part of the primary key, and use a computed column for Type in the child tables.

In your case the Type column is actually the country, so you could do something like this

CREATE TABLE machine (
  machine_id int PRIMARY KEY,
  owner_id int ...
);

CREATE TABLE country (
  country_code char(2) PRIMARY KEY,
  name varchar(50) NOT NULL
);
INSERT INTO country (country_code) VALUES ('FR', 'France'), ('DE', 'Germany');

CREATE TABLE machine_information (
  machine_id int REFERENCES machine (machine_id),
  --  record_number unnecessary it seems
  country_code char(2) REFERENCES country (country_code),
  machine_name varchar(30) NOT NULL,
  -- market_id unnecessary
  PRIMARY KEY (machine_id, country_code)
);

CREATE TABLE france_market (
  machine_id int,
  country_code AS CAST('FR' AS char(2)),
  -- France specific column data,
  PRIMARY KEY (machine_id, country_code),
  FOREIGN KEY (machine_id, country_code) REFERENCES machine_information (machine_id, country_code)
);

CREATE TABLE germany_market (
  machine_id int,
  country_code AS CAST('DE' AS char(2)),
  -- German specific column data,
  PRIMARY KEY (machine_id, country_code),
  FOREIGN KEY (machine_id, country_code) REFERENCES machine_information (machine_id, country_code)
);

Note the following:

  • country does not need a surrogate key, the two letter country code is perfect as a PK.
  • machine_information does not need a separate primary key, as its PK is made up of both the machine and country code.
  • france_market and germany_market both have the same PKs, however the country code is a fixed computed column.
  • They then have foreign keys back to machine_information.
  • This means that machine_information contains information about which table to join, and by definition only one of either france_market and germany_market can be joined.
1
  • Thank you very much for the answer and the full explanation! This looks good.
    – Kane
    Commented Feb 13, 2023 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.