Improve your database modelling skills

Solution

Schema

// there can be multiple parking lots
Table parking_lot {
  id integer [not null, unique]
  name char(255)
  // coordinates containing the parking lot
  coordinates polygon
}

// a parking lot has multiple rows for easier identification
Table parking_lot_row {
  id integer [not null, unique]
  // a row should have a name to make it findable for car owners
  name char(255)
  // coordinates containing the parking row
  coordinates polygon
  
  parking_lot_id integer [not null, ref: > parking_lot.id]
  
  Indexes {
    // a row name must be unique on a parking lot
    (name, parking_lot_id) [unique]
  }
}

// one parking lot row has multiple cells
Table parking_lot_cell {
  id integer [not null, unique]
  // name in this row to make it findable. This would be probably an incrementing number
  name char(255)
  // coordinates containing the parking row
  coordinates polygon
  parking_lot_row_id integer [not null, ref: > parking_lot_row.id]
  
  Indexes {
    // a cell name must be unique in a parking lot row
    (name, parking_lot_row_id) [unique]
  }
}

// on Solar farms, multiple solar panels are aggregated in clusters. Each parking lot has multiple clusters
Table cluster {
  id integer [not null, unique]
  parking_lot_id integer [not null, ref: > parking_lot.id]
}

// in a cluster, there can be multiple sub distributions. Those are houses, in which the upstream inverters are coming together
Table sub_distribution {
  id integer [not null, unique]
  cluster_id integer [not null, ref: > cluster.id]
}

// generated (by the panels) and released power (to the charging stations)
Table sub_distribution_input_output_history {
  id integer [not null, unique]
  sub_distribution_id integer [not null, ref: > sub_distribution.id]
  power integer [not null]
  input_output_type INPUT_OUTPUT [not null]
  created_at DATETIME
}

enum INPUT_OUTPUT {
  // from panels
  INPUT
  // to consumers
  OUTPUT
}

// an inverter aggregates multiple string combiners in one cable
Table inverter {
  id integer [not null, unique]
  sub_distribution_id integer [not null, ref: > sub_distribution.id]
}

// collect the incoming power in one inverter
Table inverter_input_history {
  id integer [not null, unique]
  inverter_id integer [not null, ref: > inverter.id]
  power integer [not null]
  created_at datetime [not null]
}

// multiple panels are combined with one string combiner
Table string_combiner {
  id integer [not null, unique]
  inverter_id integer [not null, ref: > inverter.id]
}

// a PV panel
Table panel {
  id integer [not null, unique]
  // serial number for the PV panel
  serial_number char(255) [not null, unique]
  // some panel information
  panel_status PANEL_STATUS [not null]
  // where is the panel located
  coordinates polygon [not null]
  // also assign it to a parking lot row. assigning it to a single cell is difficult b/c it can span over multiple cells
  parking_lot_row_id integer [not null, ref: > parking_lot_row.id]
  // a manufacturer can produce multiple panel types with different outputs
  panel_type_id integer [not null, ref: > panel_type.id]
  // a panel belongs to a string cominber
  string_combiner_id integer [not null, ref: > string_combiner.id]
}

// keep track of the panel inventory
enum PANEL_STATUS {
  PLANNED
  ORDERED
  ACTIVE
  DEFECT
}

Table panel_type {
  id integer [not null, unique]
  name char(255)
  // kWp
  max_power_output integer
  supplier_id integer [not null, ref: > supplier.id]
  
  Indexes {
    // there can be only one name for a panel of a supplier
    (name, supplier_id) [unique]
  }
}

// supplier/manufacturer of panels and charge stations
Table supplier {
  id integer [not null, unique]
  name char(255)
}

// a single charging station
Table charge_station {
  id integer [not null, unique]
  name char(255) [not null]
  // it is somewhere located in a parking row
  parking_lot_row_id integer [not null, ref: > parking_lot_row.id]
  // a charge station is connected to a sub distribution to receive power from the grid
  sub_distribution_id integer [not null, ref: > sub_distribution.id]
  // type of charge station
  charge_station_type_id integer [not null, ref: > charge_station_type.id]
}

// this is more or less the same as `panel_type` but you can enrich it with charge station specific attributes
Table charge_station_type {
  id integer [not null, unique]
  name char(255) [not null]
  supplier_id integer [not null, ref: > supplier.id]

  Indexes {
    // there can be only one name for a charge station of a supplier
    (name, supplier_id) [unique]
  }
}

// well, a car. We don't keep track of the owner
Table car {
  id integer [not null, unique]
}

// a car is connected to a charge station
Table charge_station_occupancy {
  id integer [not null, unique]
  charge_station_id integer [not null, ref: > charge_station.id]
  // this is null b/c if a charge station has no occupation, no row is added to the database
  car_id integer [not null, ref: > car.id]
  // store connection time
  connected_at datetime [not null]
  // retrieve the car's battery load status
  car_load_on_connect integer [not null]
  // disconnected_at must be null because it can not be simultaneously connected and disconnected
  disconnected_at datetime [null]
  car_load_on_disconnect [null]
}

// how much power has been transferred between the charge station and the car
Table charge_station_transaction_history {
  id integer [not null, unique]
  charge_station_occupancy_id integer [not null, ref: > charge_station_occupancy.id ]
  power integer [not null]
  direction TRANSACTION_DIRECTION [not null]
}

enum TRANSACTION_DIRECTION {
  CHARGE
  RELEASE
}

// this is pretty trivial implementation to assign cost or profit for charge station transaction
Table credit {
  id integer [not null, unique]
  charge_station_transaction_history_id integer [ not null, ref: > charge_station_transaction_history.id]
  // this can be negative and is stored as EUR
  amount double [not null]
  created_at datetime
}