How would you go about designing a database for service appointment?
Here is what i came up with so far. Let me know if there is a better way. Basically the admin should be able to build services list, work schedule. Based on this data Client should be able to make service appointment on specific days, hours.
CREATE TABLE Services(
id integer NOT NULL auto_increment,
service varchar(255) NOT NULL,
length_in_min integer NOT NULL DEFAULT 20,
capacity integer NOT NULL DEFAULT 1,
PRIMARY KEY(id));
INSERT INTO Services(id, service, length_in_min, capacity) values(1, 'ANY', 20, 1); -- if ANY allow for all service apointments
INSERT INTO Services(service, length_in_min, capacity) values('USG', 30, 1); -- if USG allow aonly USG apointments
INSERT INTO Services(service, length_in_min, capacity) values('VISION', 10, 1); -- if VISION allow aonly VISION apointments
CREATE TABLE Shedules(
id integer NOT NULL auto_increment,
day integer NOT NULL,
open time NOT NULL,
close time NOT NULL,
service_id integer NOT NULL DEFAULT 1,
createdat timestamp DEFAULT NOW(),
modifiedat timestamp DEFAULT NOW(),
starts_from datetime NOT NULL DEFAULT NOW(),
ends_at datetime NOT NULL DEFAULT '9999-01-01 00:00:00',
PRIMARY KEY(id),
FOREIGN KEY (service_id) REFERENCES Services(id)
ON DELETE CASCADE
ON UPDATE CASCADE);
INSERT INTO Shedules(service_id, day, open, close) values(1, 1, '10:00:00', '18:00:00');
INSERT INTO Shedules(service_id, day, open, close) values(2, 3, '08:30:00', '16:00:00');
INSERT INTO Shedules(service_id, day, open, close) values(3, 4, '08:30:00', '16:00:00');
SELECT sh.id as shedule_id, sh.day, sh.open, sh.close, sh.service_id, sh.starts_from, sh.ends_at, se.id as service_id, se.service, se.length_in_min
FROM Shedules sh
LEFT JOIN Services se
ON sh.service_id = se.id;
CREATE TABLE Clients(
id integer NOT NULL auto_increment,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
phone varchar(255) NOT NULL,
legals BOOLEAN DEFAULT TRUE,
PRIMARY KEY(id));
INSERT INTO Clients(name, email, phone) VALUES ("Wiktor", "[email protected]", "000 000 000");
CREATE TABLE Appointments(
id integer NOT NULL auto_increment,
service_id int NOT NULL, -- service int
client_id int NOT NULL, -- client int
createdat timestamp DEFAULT NOW(),
modifiedat timestamp DEFAULT NOW(),
starts datetime NOT NULL,
ends datetime NOT NULL,
paid_online BOOLEAN NOT NULL DEFAULT FALSE,
approved_by_client BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(id),
FOREIGN KEY (service_id) REFERENCES Services(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (client_id) REFERENCES Clients(id)
ON DELETE CASCADE
ON UPDATE CASCADE);
// insert in to appointments if ...