CREATE TABLE users (
user_id int NOT NULL AUTO_INCREMENT,
user_name varchar(50) NOT NULL,
password varchar(50) NOT NULL,
role varchar(50) NOT NULL,
PRIMARY KEY (user_id),
INDEX (user_name),
INDEX (user_id)
);
CREATE TABLE records (
record_id int NOT NULL AUTO_INCREMENT,
user_name varchar(50),
user_id int,
work_type varchar(100),
time_start datetime,
time_end datetime,
record_date datetime,
approved boolean,
PRIMARY KEY (record_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (user_name) REFERENCES users(user_name)
);
INSERT INTO users(user_name,password,role)
VALUES ('Orlandson Asturiano','password123','employee');
INSERT INTO users(user_name,password,role)
VALUES ('Bob Billy','password12356','admin');
SELECT * FROM users;
INSERT INTO records(user_name, user_id, work_type, time_start, time_end, record_date, approved)
VALUES(
(SELECT user_name FROM users WHERE user_id = 1),
(SELECT user_id FROM users WHERE user_id = 1),
'Programming',
'2023-05-23 08:30:00',
'2023-05-23 14:30:00',
'2023-05-23',
false);
INSERT INTO records(user_name, user_id, work_type, time_start, time_end, record_date, approved)
VALUES(
(SELECT user_name FROM users WHERE user_id = 1),
(SELECT user_id FROM users WHERE user_id = 1),
'Testing',
'2023-05-24 010:30:00',
'2023-05-24 16:30:00',
'2023-05-24',
true);
SELECT * FROM records;