1
\$\begingroup\$

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", "some@email.com", "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 ...
\$\endgroup\$
2
  • 1
    \$\begingroup\$ What version of MySQL? \$\endgroup\$
    – Reinderien
    Jul 10 at 13:30
  • 1
    \$\begingroup\$ @Reinderien I currently use 10.4.19-MariaDB \$\endgroup\$
    – DevWL
    Jul 10 at 13:38
3
\$\begingroup\$
  • 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", "some@email.com", "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

\$\endgroup\$
6
  • 1
    \$\begingroup\$ "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? \$\endgroup\$
    – Mast
    Jul 10 at 16:47
  • 1
    \$\begingroup\$ I was thinking the same thing, and wondering why VALUES wasn't consistently upper-case in the question. \$\endgroup\$ Jul 10 at 17:14
  • \$\begingroup\$ @Mast as with every other language - keyword emphasis is what a syntax-highlighting editor is for \$\endgroup\$
    – Reinderien
    Jul 10 at 20:02
  • 2
    \$\begingroup\$ 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] \$\endgroup\$
    – radarbob
    Jul 10 at 22:16
  • 1
    \$\begingroup\$ 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. \$\endgroup\$
    – radarbob
    Jul 10 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.