The following screenshot is a real-world sample from a catering service

Design a database model based upon the screenshot/PDF above.
- Orders are transferred from the PDF to the database; there is no need for a user authentication
- Each item is available through a defined menu
- Let's assume that the shop only delivers to customers in the United States
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
}