Need help on concept for database with kind of recursive data

Question

I have data which describes maintain specifications for devices. To make it easy lets say I get the following specs from the global engineering team:

Device A: Needs 3 maintain jobs a year

Device B: Needs 4 maintain jobs a year

Device C: Needs 5 maintain jobs a year

Device D: Needs 6 maintain jobs a year

Now it is possible that a Pole like EMEA (Europe/Middle East/Africa) is changing some of this specs to fulfill their legal rights. eg.

Device A: Needs 3 maintain jobs a year

Device B: Needs 2 maintain jobs a year

Device C: Needs 4 maintain jobs a year

Device D: Needs 6 maintain jobs a year

Not enough, Germany will also have some changes depending on the changes EMEA did:

Device A: Needs 3 maintain jobs a year

Device B: Needs 2 maintain jobs a year

Device C: Needs 1 maintain jobs a year

Device D: Needs 6 maintain jobs a year

This can lead further to county, city etc. So it would be a good idea to make it endless and not fix in layers.

To store the connecting data I thought I will setup two tables like

table stages
id INT PRIMARY KEY AUTOINCREMENT
name VARCHAR(100)

table stagetypes
id INT PRIMARY KEY AUTOINCREMENT
name INT
stage INT
priority INT

which will hold the entries

table stages
1 Global
2 EMEA
3 Germany
4 Bavaria
5 Munich

table stagetypes
1 1 null 0 // Global setup on nothing priority 0
2 2 2    1 // EMEA setup on EMEA priority 1
3 2 1    0 // EMEA setup on Global priority 0
4 3 3    2 // Germany setup on Germany priority 2
5 3 2    1 // Germany setup on EMEA priority 1
6 3 1    0 // Germany setup on Global priority 0

With this tables I can easily query the needed stages for a given stage. Lets say for Germany I can get all needed stages with

SELECT sstage.stage 
FROM stagetypes
JOIN stages AS mstage ON (stage.id = stagetypes.name)
JOIN stages AS stage ON (stage.id = stagetypes.stage)
WHERE mstage.name = 'Germany'
ORDER BY stagetypes.priority DESC

Result will be an array with

Germany, EMEA, Global

So far so good.

Now I can create a specification table:

table specs
id INT PRIMARY KEY AUTOINCREMENT
device VARCHAR(100)
numberofvisits INT
stage INT

and fill it with data like

table specs
1 DeviceA 3 1
2 DeviceB 4 1
3 DeviceC 5 1
4 DeviceD 6 1
5 DeviceB 2 2 // Change for EMEA
6 DeviceC 4 2 // Change for EMEA
7 DeviceC 1 3 // Change for Germany

But now I have no idea how to fetch the specs for Germany of all Devices.

Result should be

1 DeviceA 3 1
4 DeviceD 6 1
5 DeviceB 2 2
7 DeviceC 1 3

So how can I design the specs table to get this data with a most simple query? Or do you have a complete different approach to solve this problem?

At the end I am restricted to use mySQL5.7 or OracleSQL11. So ranking is not a possible solution :(

Answer 1

Ranking rows in a modern DBMS (Oracle, PostgreSQL, MySQL starting with version 8, SQL Server, etc.) is very easy with the ROW_NUMBER function.

If the data model were this:

institutions

+----------------+---------+----------+
| institution_id |  name   | priority |
+----------------+---------+----------+
|              1 | Global  |        0 |
|              2 | EMEA    |        1 |
|              3 | Germany |        2 |
+----------------+---------+----------+

setups

+-----------+---------------+-----------+
| device_id | instituion_id | job_count |
+-----------+---------------+-----------+
| A         |             1 |         3 |
| B         |             1 |         4 |
| C         |             1 |         5 |
| A         |             2 |         3 |
| B         |             2 |         2 |
| A         |             3 |         3 |
+-----------+---------------+-----------+

then you'd select the most relevant setup per device as follows:

select *
from setups s
join institutions i using (instituion_id)
order by row_number() over (partition by s.device_id order by i.priority desc)
fetch first rows with ties;

(This is standard SQL syntax and should work in Oracle and PostgreSQL. For SQL Server and MySQL you'd write the query a little different.)

Using an old DBMS such as MySQL 5 just makes writing the query a tad more clumsy, that's all. Here is a solution with NOT EXIST (get the setups where not exists a better setup for the device):

select *
from setups s
join institutions i using (instituion_id)
where not exists
(
  select null
  from setups sx
  join institutions ix using (instituion_id)
  where sx.device_id = s.device_id
  and ix.priority > i.priority
);