Events Scheduling Database Design

This writing documents the design and creation of a database to support the scheduling and operation of events at an Institution. I'll be using Data Definition Language commands in most of the implementation. If you're not conversant with DDL commands and database design, I've left links to some resources at the end of this writing. You can go over them and return to the article. Also, this task is from a course I took on Coursera, although I have modified the design slightly.

Database design is never a one-off activity. It is essentially an iterative process where each iteration seeks to ensure that every element required by the business is present and that redundancy is beaten to a minimum. This process is technically referred to as Normalization. Richard Peterson gives a very good explanation of what Normalization entails here. The final design at the end of this writing is the result of iterating over rough designs to improve them.

Now, to the business scenario.

The diagram below shows the process followed to request for an event to be held at the institution.

A customer (inside or outside the institution) requests for an event to hold at a location within a facility.

Each event request states details of the event, such as the customer's name, the date requested, and so on.
If the request is denied, no further actions are taken.
However, if the event is approved, event plans are created, and an employee is assigned to manage each plan.
Event plans detail different activities required for the event, e.g., setup, clean-up, operation, security, etc.
Every event plan has one or more event plan lines connected to it. The event plan lines detail different resources required for the activities in their connected event plans. Resources may include personnel, services, items, etc.

The classes of objects in this scenario are ;

Customer
Location
Facility
Event Request
Event Plan
Employee
Event Plan Line
Resource

The ER diagram below shows the final logical design for the database created using draw.io.

The database is predominated by many-to-one relationships. However, there is an exception with the EventPlan and Employee table's relationship. Here, we see a many-to-one-or-zero relationship. This is because, at creating an event's plan, an employee may not have been assigned to the plan.

The create table statements for the tables are below.

CREATE TABLE Customer (CUSTNO     CHAR(8),
               CUSTNAME varchar(30) NOT NULL,
               ADDRESS  varchar(30),
             INTERNAL char(2),
              PHONE      decimal(12,2),
              CITY     varchar(25),
              STATE    char(2),
              ZIP      int,
              CONSTRAINT PKCustomer PRIMARY KEY (CUSTNO));




create table Employee (EMPNO     varchar(8) primary key not null,
                      EMPNAME    varchar(20) not null,
                      DEPARTMENT varchar(20) not null,
                      EMAIL      varchar(20) not null,
                      PHONE      varchar(8) not null);

Create table Resource (RESNO   varchar(8)primary key not null,
                          RESNAME varchar(20),
                          RATE    int check(RATE>0));

create table Facility (FACNO   char(4) primary key not null,
                       FACNAME varchar(20));

create table Location (LOCNO   char(4) primary key not null,
                       FACNO   char(4) not null,
                       LOCNAME varchar(20) not null,
                      constraint fk_lfac foreign key(FACNO) references Faclity(FACNO));

Create table EventRequest (EVENTNO     varchar(8) primary key not null,
                           DATEHELD    date not null,
                           DATEREQ     date not null,
                           CUSTNO      varchar(8) not null,
                           LOCNO       varchar(8) not null,
                           DATEAUTH    date ,
                           STATUS      varchar(8) not null check (STATUS in('Pending','Denied','Approved')),
                           ESTCOST     int not null,
                           ESTAUDIENCE int not null check (ESTAUDIENCE>0),
                          constraint fk_ELOC foreign key(LOCNO) references Location(LOCNO),
                          constraint fk_Ecust foreign key(CUSTNO) references Customer(CUSTNO));


Create table EventPlan (PLANNO   varchar(8) primary key not null,
                        EVENTNO  varchar(8)not null,
                        WORKDATE date not null,
                        NOTES    varchar(30),
                        ACTIVITY varchar(10) not null,
                        EMPNO    varchar(8),
                       constraint fk_Eventno foreign key(EVENTNO) references EventRequest(EVENTNO),
                       constraint fk_Empno foreign key(EMPNO) references Employee(EMPNO));


Create table EventPlanLine (PLANNO    varchar(8) not null,
                            LINENO    varchar(8) not null,
                            TIMESTART timestamp check( TIMESTART<TIMEEND),
                            TIMEEND   timestamp,
                            NUMBERFLD int,
                            LOCNO     varchar(8) not null,
                            RESNO     varchar(8) not null,
                           constraint p_key primary key (PLANNO,LINENO),
                           constraint fk_ELocno foreign key(LOCNO) references Location(LOCNO),
                           constraint fk_Eresno foreign key(RESNO) references Resource(RESNO),
                           constraint fk_Eplanno foreign key (PLANNO) references EventPlan(PLANNO));