Solution

Schema

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

Table organization {
  id integer [primary key, increment]
  // name of company, organization or buyer
  name char(255) [not null]
  address text [not null]
  city text [not null]
  phone char(255) [not null]
  alternate_phone char(255) [not null]
  // statecode in the USA are 2 chars
  statecode char(2) [not null]
  // 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]
  confirmation_fax char(255) [null]
  confirmation_mail char(255) [null]
}

// there could be multiple contacts per organization;
Table contact {
  id integer [primary key, increment]
  // this is the "Name" line
  firstname char(255) [not null]
  lastname char(255) [not null]
  organization_id integer [not null, ref: > organization.id]
}

Table order {
  id integer [primary key, increment]
  // how does the customer wants to pay?
  payment_method payment_type [not null, default:`CASH`]
  // wants the customer to pickup the order or should it be delivered
  delivery_type delivery_type [not null, default:`PICKUP`]
  // this is a cominbation for the "Date of delivery or pickup" and "Time of delivery or pickup" field
  delivery_at datetime [not null]
  instructions text [null]
  // distance if delivery_type == DELIVERY; null if order is picked up
  delivery_distance integer [null]
  contact_id integer [not null, ref: > contact.id]
}

Table item_in_order {
  id integer [primary key, increment]
  order_id integer [not null, ref: > order.id]
  item_id integer [not null, ref: > item.id]
  quantity integer [not null]
  // the price can change over time. this means that during INSERT of an item_in_order we have to copy the value from item.price into this column.
  // a change in a item price must not be lead to a different sum of an order which has already happened.
  price decimal(8,2) [not null]
}

Table item {
  id integer [primary key, increment]
  // name of the item
  name char(255) [not null]
  // default price of item; assume that we only sell in the same country
  price decimal(8,2) [not null]
}

// A post can only one have these status
Enum payment_type {
  CASH
  CREDIT_CARD
}

// A post can only one have these status
Enum delivery_type {
  PICKUP
  DELIVERY
}