TableCheck

Prototype for table availability check

Description and assumptations

Double booking table exclusion is supported by PostgreSQL exclude constraint. So it must be considered when moving on another storage adapter.
Timezones are ignored in this prototype.
Reservation time borders are inclusive.
There is no passed-argument verification except for ones in changesets.

Structure

The main context modules serve as an interface and only call public functions from either query or command modules.

Command module should containt some business flow that is important for application.

Query module should contain either quering data from storage layer either simple queries that change state of database (like update_all call etc). But in this prototype even simple state change queries are in command modules.

Also inner context modules (command, query, schema) follow the same naming convention as controllers in phoenix:

type folder ommitted from module name;
module name is postfixed with type;

Example controller in phoenix: AcmeWeb.PostController. Example inner module: TableCheck.Reservations.ReservationSchema.

There three contexts:

Restaurants -> to manage restaurant and it's tables
Reservations -> to manage reservation on restaurant tables
Tables -> to query availability on restaurant tables

Entities

Restaurant

Restaurant entity itself. Has a unique autogenerated ID and the required name. No business hours were implemented in the prototype.

Tables

Table entity. Has a unique autogenerated ID and the required capacity and ID of restaurant fields.

Table capacity is the number of people that can sit at this table. We assume that capacity can only be a positive number.

We assume that tables in our system were created by one, and we don't have a general table inventory.

Guests

Entity represents required information about the person who reserves a table: name, phone, email. Also, specific guests are linked to a specific restaurant. We assume guest uniqueness by restaurant and phone number.

For simplicity, we could include this information to reservations, but as a general rule, it's a good solution to separate different data into different tables. Maybe in the future we'll need more functionality specifically for guests.

Reservations

The entity represents a table reservation for a specific date and time. Has the required date, start_at, end_at, table_id, and guest_id fields. In this prototype, we don't have any status associated with a reservation.

Reservation uniqueness is guaranteed by the database exclusion constraint on table ID and reservation date and time range.

Also, we don't have any checks on the reservation time limit, etc. (You can create reservations that have already been passed.)

Up and running

Install erlang and elixir versions from tool versions file.
Ensure your Postgres is running and accessible on localhost:5432 with default credentials (postgres, postgres).
Fetch deps:

mix deps.get

Create database, migrate it and seed data

mix ecto.reset

Start application with iex console:

iex -S mix

Usage

Create restaurant:

iex> {:ok, restaurant} = TableCheck.Restaurants.create_restaurant(%{
    name: "My best restaurant"
    })

{:ok,
 %TableCheck.Restaurants.RestaurantSchema{
   id: 41,
   name: "My best restaurant",
   inserted_at: ~N[2024-02-21 09:53:21],
   updated_at: ~N[2024-02-21 09:53:21]
 }}

Create table for restaurant:

iex> {:ok, table} = TableCheck.Restaurants.create_table(%{capacity: 1, restaurant_id: restaurant.id})

{:ok,
 %TableCheck.Restaurants.TableSchema{
   id: 401,
   capacity: 1,
   restaurant_id: 42,
   inserted_at: ~N[2024-02-21 09:55:47],
   updated_at: ~N[2024-02-21 09:55:47]
 }}

Create reservation for restaurant table:

iex> TableCheck.Reservations.create_reservations(%{
            start_at: ~N[2024-02-19 18:00:00],
            end_at: ~N[2024-02-19 22:00:00],
            table_id: table.id,
            guest: %{
                name: "my best guest",
                phone: "some_magic_phone",
                restaurant_id: restaurant.id
            }
    })

{:ok,
 %{
   guest: %TableCheck.Reservations.GuestSchema{
     id: 32001,
     name: "my best guest",
     phone: "some_magic_phone",
     restaurant_id: 42,
     inserted_at: ~N[2024-02-21 09:57:37],
     updated_at: ~N[2024-02-21 09:57:37]
   },
   reservation: %TableCheck.Reservations.ReservationSchema{
     id: 32001,
     start_at: ~N[2024-02-19 18:00:00],
     end_at: ~N[2024-02-19 22:00:00],
     table_id: 401,
     guest_id: 32001,
     inserted_at: ~N[2024-02-21 09:57:37],
     updated_at: ~N[2024-02-21 09:57:37]
   }
 }}

Check restaurant availability:

    # all tables reserved:
    iex> TableCheck.Tables.list_available_tables(%{
        restaurant_id: restaurant.id,
        min_datetime: ~N[2024-02-19 17:00:00],
        max_datetime: ~N[2024-02-19 19:00:00]
    })

[]

    # there is available table
    iex> TableCheck.Tables.list_available_tables(%{
        restaurant_id: restaurant.id,
        min_datetime: ~N[2024-02-20 17:00:00],
        max_datetime: ~N[2024-02-20 19:00:00]
    })

[
  %TableCheck.Restaurants.TableSchema{
    id: 401,
    capacity: 1,
    restaurant_id: 42,
    inserted_at: ~N[2024-02-21 09:55:47],
    updated_at: ~N[2024-02-21 09:55:47]
  }
]

For more usage docs please refer to contexts.

Running tests

To run tests:

mix test

To run with cover:

mix test --cover

senconscious/table_check