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!

Kane
  • 113
  • 2

1 Answers1

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.
Charlieface
  • 17,078
  • 22
  • 44