How do I implement the Plans table with multiple locations from the Locations table? Like "planX" has many "locations" like NL, FR, US,...
Here I only can have one relation.
Plans table
Table plans {
id integer pk
title varchar
memory varchar
disk_type integer [ref: > disk_types.id]
storage varchar
cpu_model varchar
cpu_cores integer
uplink integer [ref: > uplinks.id]
location integer [ref: > locations.id]
price integer
provider_id integer [ref: > providers.id]
}
Locations table:
Table locations {
id integer pk
name varchar
country_code varchar
}
You can create a junction table which maps plans to locations:
CREATE TABLE plan_location (
plan_id INT,
location_id INT
);
You would use this table as follows:
SELECT p.*, l.*
FROM plans p
INNER JOIN plan_location pl
ON pl.plan_id = p.id
INNER JOIN locations l
ON l.id = pl.location_id;