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.

There are many ways to do this, but this is just an example. Please use this for your system.

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 nameLogical nameData typeNullNote
idIDintprimary
emailEmail addressvarchar(255)unique
nameShop namevarchar(255)
counterSequential orderintdefault: 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 nameLogical nameData typeNullNote
idIDintprimary
emailEmail addressvarchar(255)unique
nameShop namevarchar(255)

counters

Physical nameLogical nameData typeNullNote
idIDintprimary
shop_idShop IDint
counterSequential orderintdefault: 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 nameLogical nameData typeNullNote
idIDintprimary
emailEmail addressvarchar(255)unique
nameShop namevarchar(255)

counters: Sequential order

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

Physical nameLogical nameData typeNullNote
idIDintprimary
shop_idShop IDint
nameCounter namevarchar(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 nameLogical nameData typeNullNote
idIDintprimary
counter_idCounter IDint
nameName of person waitingvarchar(255)YES
checked_in_atDate and time the line starteddatetime
checked_out_atDate and time calleddatetimeYES
canceled_atDate and time of cancellationdatetimeYES

Follow me!

コメント

PAGE TOP
タイトルとURLをコピーしました