Appointment database design - Schema

Question

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 ...

• Reinderien: What version of MySQL?
• DevWL: @Reinderien I currently use 10.4.19-MariaDB

Answer 1

• This is a matter of personal preference, but nothing in the SQL standard imposes upper-case, and I find lower-case to be more legible
• On your primary key columns, replace NOT NULL with an inline primary key constraint declaration which implies not null anyway
• Move your foreign key constraints to inline
• length_in_min could be a floating-point quantity, if you want to represent fractional minutes
• Your repeated values clauses can in some instances be combined into one statement
• It's not "Shedule", it's "Schedule"
• Never (never) hard-code an ID, even for test data. Select based on the other columns that you know. Likewise, never set a numeric default for a foreign key value.
• Add more constraints around your datetimes - you know that modified will never be before created, for example; and the day-of-week must be between 1 and 7.
• I'm pleased to see that overall your use of boolean and non-nullable columns is actually pretty decent.
• Your createdat and modifiedat do not follow your underscore convention for column names, so add some underscores.

Suggested

create table Services(
 id integer auto_increment primary key,
 service varchar(255) not null,
 length_in_min float not null default 20,
 capacity integer not null default 1
);
create table Schedules(
 id integer auto_increment primary key,
 day integer not null check(day between 1 and 7),
 open time not null,
 close time not null check(close > open),
 service_id integer not null references Services(id)
 on delete cascade on update cascade,
 created_at timestamp default now(),
 modified_at timestamp default now() check(modified_at >= created_at),
 starts_from datetime not null default now(),
 ends_at datetime not null default '9999-01-01 00:00:00' 
 check(ends_at >= starts_from)
);
create table Clients(
 id integer auto_increment primary key,
 name varchar(255) not null,
 email varchar(255) not null,
 phone varchar(255) not null,
 legals boolean default true
);
create table Appointments(
 id integer auto_increment primary key,
 service_id int not null references Services(id)
 on delete cascade on update cascade,
 client_id int not null references Clients(id)
 on delete cascade on update cascade,
 created_at timestamp default now(),
 modified_at timestamp default now() check(modified_at >= created_at),
 starts datetime not null,
 ends datetime not null check(ends > starts),
 paid_online boolean not null default false,
 approved_by_client boolean not null default false
);
insert into Services(service, length_in_min, capacity) values
 ('ANY', 20, 1), -- if ANY allow for all service apointments
 ('USG', 30, 1), -- if USG allow only USG apointments
 ('VISION', 10, 1); -- if VISION allow only VISION apointments
 
insert into Schedules(service_id, day, open, close) 
select id, 1, '10:00:00', '18:00:00' from Services where service = 'ANY';
insert into Schedules(service_id, day, open, close)
select id, 3, '08:30:00', '16:00:00' from Services where service = 'USG';
insert into Schedules(service_id, day, open, close)
select id, 4, '08:30:00', '16:00:00' from Services where service = 'VISION';
insert into Clients(name, email, phone) values ("Wiktor", "[email protected]", "000 000 000");
select sh.id as schedule_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 Schedules sh 
left join Services se on sh.service_id = se.id;

output table

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=fc5aba0479e743e9dff869c5635579fd

• Mast: "This is a matter of personal preference, but nothing in the SQL standard imposes upper-case, and I find lower-case to be more legible" It used to be standard to write keywords in uppercase and it's still the standard in many places. Primary benefit of using uppercase is it's immediately obvious they're keywords. Did this change recently?
• Toby Speight: I was thinking the same thing, and wondering why VALUES wasn't consistently upper-case in the question.
• Reinderien: @Mast as with every other language - keyword emphasis is what a syntax-highlighting editor is for
• radarbob: double Plus Good for Never (never) hard-code an ID [i.e. meaningless PK] ... never set a numeric default for a foreign key valueI [i.e. self-corrupting database]
• radarbob: I don't understand the seeming universal urge to create "dummy" PKs especially with disregard for candidate compound PKs. Either case, in my experience, this may end up with the application code heroically enforcing key rules; with sporatic code fixes to handle the latest corrupt record variation. "Candidate" = A relationally valid key, but not implemented in the DB.