what is a better way to create table?

Question

I have mechanic that provide some services and there may be several services provided by a mechanic and how is better way to make tables in this case? What i have now is :
Mechanic table :

create table mechanic(
id_master int not null unique,
rating int DEFAULT 10,
start_time TIME not null,
end_time TIME not null,
work_or_not BOOL DEFAULT 1,
Constraint FK_masterID_userID FOREIGN KEY(id_master) REFERENCES user(id_user),
Constraint Check_STARTTIME_ENDTIME check(start_time<end_time),
Constraint CHECK_FOR_STARTTIME CHECK(start_time>'-1:0:0' AND HOUR(start_time)<23),
Constraint CHECK_FOR_ENDTIME CHECK(end_time>'-1:0:0' AND HOUR(end_time)<23));

and i need to show what services mechanic provides and what table should i create ? Maybe services table that ref to mechanic table via foreign key but i also need to store services values, so what should i do?

for example if i have several services and price for them and mechanic could provide some of them of all of them, so what table for services i should create to make that relationship between mechanic and services he/she provides? — besidethejokes, May 31 '21 at 11:11
If some mechanic may provide different services and some service may be provided by different mechanics then this is many-to-many (M:N) link which needs in additional adjacency table. — Akina, May 31 '21 at 11:13
@Akina, yes thanks im now making that relationship , if you post your comment as answer i mark it as right — besidethejokes, May 31 '21 at 11:31

score 1 · Accepted Answer · answered May 31 '21 at 11:35

If some mechanic may provide different services and some service may be provided by different mechanics then this is many-to-many (M:N) link which needs in additional adjacency table.

CREATE TABLE mechanic ( id_mechanic INT AUTO_INCREMENT PRIMARY KEY
                   -- , ... {another columns}
                      );

CREATE TABLE service ( id_service INT AUTO_INCREMENT PRIMARY KEY
                  -- , ... {another columns}
                     );

CREATE TABLE adjacency ( id_mechanic INT NOT NULL,
                         id_service INT NOT NULL,
                         PRIMARY KEY (id_mechanic, id_service),
                         FOREIGN KEY (id_mechanic) REFERENCES mechanic (id_mechanic),
                         FOREIGN KEY (id_service) REFERENCES service (id_service)
                       );

what is a better way to create table?

1 Answers1