sqlpostgresqldbml

How to implement a table with one field having multiple rows in another table


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
}

Solution

  • 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;