DB design for a “Waiting list system”

This article presents a proposal for a DB design related to a "Waiting list system".

Some types are described, from very simple versions to those with multiple functions.

This article describes a suggested DB design.
This design is just an example.
Please use this as a template and adapt it to the system you wish to build.

Very simple
1. ERD
2. Tables
  1. shops
Manage multiple waiting list
1. ERD
2. Tables
  1. shops
  2. counters
Keep records
1. Tables

Very simple

ERD

No difficult functions at all. You just to know the current number of people on the waiting list.

erDiagram shops shops { int id varchar email varchar name int counter }

Tables

shops

Physical name	Logical name	Data type	Note
id	ID	int	primary
email	Email address	varchar(255)	unique
name	Shop name	varchar(255)
counter	Sequential order	int	default: 0

Manage multiple waiting list

A single store can manage waiting list.

Use the "counters.counter" by incrementing it.

Possible use cases include multiple contact points, multiple products, and multiple branches.

ERD

erDiagram shops ||--o{ counters : hasMany shops { int id varchar email varchar name } counters { int id int shop_id int counter }

Tables

shops

Physical name	Logical name	Data type	Note
id	ID	int	primary
email	Email address	varchar(255)	unique
name	Shop name	varchar(255)

counters

Physical name	Logical name	Data type	Note
id	ID	int	primary
shop_id	Shop ID	int
counter	Sequential order	int	default: 0

Keep records

It keeps track of when a customer arrives and when they are called. Cancellations can also be made.

The number of people waiting is the number of counter_id specified in counter_histories excluding those containing checked_out_at and cancelled_at. (Refer to the SQL below)

SELECT COUNT(*) FROM counter_histories WHERE checked_out_at IS NULL OR canceled_at IS NULL;

SQL

With this level of functionality, it could be operated as a service at a minimum.

erDiagram shop ||--o{ counters : hasMany counters ||--o{ counter_histories : hasMany shop { int id varchar email varchar name } counters { int id int shop_id varchar name } counter_histories { int id int counter_id varchar name datetime checked_in_at datetime checked_out_at datetime canceled_at }

Tables

shops: Shop

Physical name	Logical name	Data type	Note
id	ID	int	primary
email	Email address	varchar(255)	unique
name	Shop name	varchar(255)

counters: Sequential order

"name" column is supposed to include the date or an something name.

Physical name	Logical name	Data type	Note
id	ID	int	primary
shop_id	Shop ID	int
name	Counter name	varchar(255)

counter_histories: Counter history

"name" column is assumed to be the name of the person waiting, the medical ticket number, or something else that will be displayed in the public queue. "checked_out_at" column puts the date and time when it is called. I was a little unsure if this was the right way to word it, so if you know the proper column name, it would be helpful if you could point it out.

Physical name	Logical name	Data type	Null	Note
id	ID	int		primary
counter_id	Counter ID	int
name	Name of person waiting	varchar(255)	YES
checked_in_at	Date and time the line started	datetime
checked_out_at	Date and time called	datetime	YES
canceled_at	Date and time of cancellation	datetime	YES