Designing A Booking System For A Beauty Salon: Entity-Relationship And Global Relation Diagram

The entity relationship is modelled based on the following assumptions.

• There exists bookings made by registered customers. Each booking can have one or more services associated with it.
• Non-regular appointments are made by those customers who are not registered as clients and each appointment can have one more services associated with it.
• A booking services for a client or a non-regular client can be performed by more than one therapist. For example for just one booking with multiple services, the client can get a massage from a different therapist and then proceed to get waxing from a different therapist and all this is within the same booking. The same case applies to an appointment from non-regular customers.
• For time-based service a staff has his or her own rate depending on their expertise. A staff can be an expert in one or more time-based services but all staff can do item-based service.

create table client (

clientID integer primary key,

firstName varchar(50) not null,

lastname varchar(50) not null,

contact varchar(25) not null

create table staff (

staffID integer primary key,

firstName varchar(50) not null,

lastname varchar(50) not null,

address varchar(100) not null

create table nonregular_appointment (

appointmentID integer primary key,

appointmentDate date not null,

appointmentTime varchar(15) not null,

cost decimal(8,2)

create table service (

serviceID integer primary key,

name varchar(50) not null,

serviceType varchar(25) not null

create table booking(

bookingID integer primary key,

clientID integer not null,

cost decimal(8,2) not null,

dateBooked date not null,

timeBooked varchar(15) not null,

foreign key (clientID) references client (clientID)

create table time_based (

serviceID integer primary key,

chargeBasis decimal not null,

foreign key (serviceID) references service (serviceID);

create table item_based (

serviceID integer primary key,

rate decimal not null,

foreign key (serviceID) references service (serviceID)

create table staff_services (

staffID integer not null,

serviceID integer not null,

rate decimal not null,

primary key (serviceID,staffID),

foreign key (serviceID) references time_based (serviceID),

foreign key (staffID) references staff (staffID)

create table booked_services (

bookingID integer not null,

serviceID integer not null,

staffID integer not null,

primary key (serviceID,bookingID),

foreign key (serviceID) references service (serviceID),

foreign key (staffID) references staff (staffID),

foreign key (bookingID) references booking (bookingID)

create table appointment_services (

appointmentID integer not null,

serviceID integer not null,

staffID integer not null,

primary key (serviceID,appointmentID),

foreign key (serviceID) references service (serviceID),

foreign key (staffID) references staff (staffID),

foreign key (appointmentID) references nonregular_appointment (appointmentID)

insert into client (clientID, firstName, lastName, contact) values (‘1′,’Lebron’,’James’,’+3342343123′);

insert into client (clientID, firstName, lastName, contact) values (‘2′,’Kevin’,’Durant’,’+3342343123′);

insert into client (clientID, firstName, lastName, contact) values (‘3′,’Steph’,’Curry’,’+3342343123′);

insert into staff (staffID, firstName, lastName, address) values (‘1′,’Jon’,’Snow’,’winterfell’);

insert into staff (staffID, firstName, lastName, address) values (‘2′,’Arya’,’Stark’,’winterfell’);

insert into staff (staffID, firstName, lastName, address) values (‘3′,’Cersei’,’Lannister’,’kings landing’);

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values (‘1′,’2018-12-12′,’10:00AM’);

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values (‘2′,’2018-12-12′,’11:00AM’);

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values (‘3′,’2018-12-12′,’12:00PM’);

insert into service (serviceID,name, serviceType) values (1,’massage’,’time-based’);

insert into service (serviceID,name, serviceType) values (2,’waxing’,’time-based’);

insert into service (serviceID,name, serviceType) values (3,’Nails’,’item-based’);

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (1,1,233,’2018-12-12′,’9:00AM’);

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (2,2,433,’2018-12-12′,’10:00AM’);

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (3,3,321,’2018-12-12′,’11:00AM’);

insert into time_based (serviceID, chargeBasis) values (1,1);

insert into time_based (serviceID, chargeBasis) values (2,0.5);

insert into item_based(serviceID, rate) values (1,12);

insert into staff_services (staffID, serviceID, rate) values (1,1,12);

insert into staff_services (staffID, serviceID, rate) values (2,2,32);

insert into staff_services (staffID, serviceID, rate) values (1,2,21);

insert into booked_services(bookingID, serviceID, staffID) values (1,1,1);

insert into booked_services(bookingID, serviceID, staffID) values (2,2,2);

insert into booked_services(bookingID, serviceID, staffID) values (1,2,3);

insert into appointment_services(appointmentID, serviceID, staffID) values (1,1,1);

insert into appointment_services(appointmentID, serviceID, staffID) values (2,2,2);

insert into appointment_services(appointmentID, serviceID, staffID) values (1,2,3);