Solution

Schema

The diagram can also be found online on dbdiagram.io.

Table vehicle_category {
  id integer [not null, unique]
  per_hour decimal(6,2) [not null]
  per_km_up_to_100 decimal(6,2) [not null]
  per_km_from_101 decimal(6,2) [not null]
}

Table vehicle {
  id integer [not null, unique]
  name char(255) [not null, unique]
  vehicle_category_id integer [not null, ref: > vehicle_category.id]
}

Table customer {
  id integer [not null, unique]
  number char(10) [not null, unique]
  name char(255) [not null, unique]
  // ZIP code in USA is 10 characters max
  // @see https://www.oreilly.com/library/view/regular-expressions-cookbook/9781449327453/ch04s14.html
  zip char(10) [not null]
  city char(255) [not null]
  phone char(20) [not null]
  street char(255) [not null]
}

Table invoice {
  id integer [not null, unique]
  number char(10) [not null, unique]
  created_at date [not null]
  // this could be calculated by the newest date in the invoice_item table
  period_end_at date [not null]
  customer_id integer [not null, ref: > customer.id]
}

Table invoice_item {
  id integer [not null, unique]
  begin_at datetime [not null]
  end_at datetime [not null]
  amount integer [not null]
  net decimal(8,2) [not null]
  gross decimal(8,2) [not null]
  vehicle_id integer [not null, ref: > vehicle.id]
  invoice_id integer [not null, ref: > invoice.id]
}

Table account {
  id integer [not null, unique]
  iban char(34) [not null, unique]
  is_primary boolean [not null, default: true]
  customer_id integer [not null, ref: > customer.id]
}