SQL Database Design For Developers at Longhorn PHP 2025

Databases are the magic box in a lot of our workflows and in this presentation I show you my rules for better database design,

1. SQL Database Design For Developers Scott Keck-Warren Longhorn PHP 2025 https://tinyurl.com/todo https://scott.keck-warren.com/

2. Never Worked With a DBA On a Project Every Project Has At Least One SQL Database

3. Never Worked With a DBA On a Project Every Project Has At Least One SQL Database

4. My (Early) Relationship to SQL Database: Less Than Ideal

5. How Did I Get Better?

6. Trial By Fire

7. Problems “Random” slowness Data inconsistencies Weird Bugs

8. SQL Database Design For Developers

9. Scott Keck-Warren

10. Partner “Best Dad Ever”

11. PHP Developer

12. Educator

13. YouTube Videos Articles TikToks Conference Speaker

14. Podcaster

15. Community Corner Podcast

16. https://scott.keck-warren.com

17. Scott’s Rules For Database Design

18. Never Worked With a DBA On a Project

19. Scott’s Rules For Database Design Backup Your Database and Test It Normalize Your Database For Data Deduplication Use The Database Engine to Keep Data Clean Proactively Add Indexes to Keep Queries Performant Denormalize Your Database For Speed

20. Backup Your Database and Test It

21. “Backups are unimportant” “Backups are the most important thing in the world” One “Lost” File

22. mysql> delete from users where id = 1;

23. mysql> delete from users where id > 1;

24. Backup Your Database and Test It 3 - 2 - 1 Backups 3 copies 2 different mediums 1 “offsite”

25. Backup Your Database and Test It Router Router Internet Internet Internet Switch Switch Application Backups

26. Backup Your Database and Test It Router Router Internet Internet Internet Switch Switch Application Backups

27. Backup Your Database and Test It Router Router Internet Internet Internet Switch Switch Application Backups

28. Backup Your Database and Test It Router Router Internet Internet Internet Switch Switch Application Backups

29. Backup Your Database and Test It Internet Internet Internet Switch Switch Application Backups

30. Backup Your Database and Test It At Least Monthly Test Restoring It Ideally as part of DR plan

31. How Should I Backup My Data?

32. Backup Your Database and Test It 4 Types of Backups Continuous Data Protection - Constantly backing up changes Full - All Data Differential - All changes from last full Incremental - All changes from previous backup

33. Backup Your Database and Test It Continuous Data Protection Ideal method to backup data Database don’t generally support this

34. Backup Your Database and Test It Always Full Backups Makes it really easy to do a restore When DB is small - perfect When DB is not so small?

35. Grandfather-Father-Son Backups

36. Grandfather-Father-Son Backups Grandfather - full offsite backup for long term storage Father - full onsite backup for quick restores Son - differential backup for quick backup

37. Full Backups In MySQL mysqldump —single-transaction > full_backup.sql

38. Partial Backups In MySQL mysqldump —single-transaction \ —-where=“updated_at>={last_full}” > partial_backup.sql

39. Partial Backups In MySQL # in / etc / mysql / mysql.conf.d / mysqld.cnf Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10

40. Partial Backups In MySQL update users set x=“y” where id = 1 users /var/ log /mysql/mysql-bin.######

41. Partial Backups In MySQL # Create full backup and # 1. Start a new binary log # 2. Export the current binary log state mysqldump —single-transaction --flush-logs \ --master-data=2 > full_backup.sql

42. Partial Backups In MySQL # replay each “new” binary log mysqlbinlog /var/ log /mysql/mysql-bin.000002 | \ mysql -uroot -p mydb

43. Normalize Your Database For Data Deduplication

44. Users Table

45. Users Table Email address Password Active state Hire Date Listing of previous passwords Office Name Office City Office Zip

46. Users Table Email address (string) Password (string) Active state (string) Hire Date (string) Listing of previous passwords (string) Office Name (string) Office City (string) Office Zip (string)

47. Users Table email password active hire_date previous_password office_name office_phone office_city office_zip [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 [email protected] NULL 1 8/11/2024 hash2
hash7
hash8 main office 5555555555 Saginaw 48609 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

48. Normalize Your Database For Data Deduplication “[T]he process of structuring a relational database  in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity .” -“Database normalization” on Wikipedia

49. Normalize Your Database For Data Deduplication UNF: Unnormalized form 1NF: First normal form 2NF: Second normal form 3NF: Third normal form EKNF: Elementary key normal form BCNF: Boyce–Codd normal form 4NF: Fourth normal form ETNF: Essential tuple normal form 5NF: Fifth normal form DKNF: Domain-key normal form 6NF: Sixth normal form

50. Normalize Your Database For Data Deduplication UNF: Unnormalized form 1NF: First normal form 2NF: Second normal form 3NF: Third normal form EKNF: Elementary key normal form BCNF: Boyce–Codd normal form 4NF: Fourth normal form ETNF: Essential tuple normal form 5NF: Fifth normal form DKNF: Domain-key normal form 6NF: Sixth normal form

51. Normalize Your Database For Data Deduplication Boyce–Codd Normal Form: X should be a superkey for every functional dependency (FD) X−>Y in a given relation.

52. Unnormalized Form

53. Unnormalized Form A table doesn’t meet any of the conditions of normalization Essentially a spreadsheet email password active hire_date previous_password office_name office_phone office_city office_zip [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 [email protected] NULL 1 8/11/2024 hash2
hash7
hash8 main office 5555555555 Saginaw 48609 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

54. First Normal Form (1NF)

55. First Normal Form (1NF) The table contains a unique identifier, also called the primary key, that is used to identify the row. Each column contains atomic values (values that can not be broken down)

56. 1NF - users email password active hire_date previous_password office_name office_phone office_city office_zip [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 [email protected] NULL 1 8/11/2024 hash2
hash7
Hash8 main office 5555555555 Saginaw 48609 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

57. 1NF - users In MOST cases, a unique identifier should be: Auto-incrementing int -> good “fast” solution UUID -> slower but better for data shared across systems

58. 1NF - users id email password active hire_date previous_password office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 hash2
hash7
Hash8 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

59. 1NF - users id email password active hire_date previous_password office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 hash2
hash7
hash8 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

60. 1NF - user_password_histories

61. 1NF - user_password_histories id user_id password

62. 1NF - user_password_histories id user_id password 1 1 hash1 2 1 hash5 3 1 hash6 4 2 hash2 5 2 hash7 6 2 hash8 7 3 hash3 8 4 hash4

63. 1NF - users id email password active hire_date previous_password office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 hash1
hash5
hash6 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 hash2
hash7
Hash8 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 hash3 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday hash4 Main 555/555/5555 Saginaw 48609

64. 1NF - users id email password active hire_date office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday Main 555/555/5555 Saginaw 48609

65. Second Normal Form (2NF)

66. Second Normal Form (2NF) Is already in 1NF All the non-key columns are dependent on the primary key of the table

67. Second Normal Form (2NF) id email password active hire_date office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday Main 555/555/5555 Saginaw 48609

68. 2nd - offices id name phone city zip 1 Main Office 555-555-5555 Saginaw 48609 2 main office 5555555555 Saginaw 48609 3 Man office [phone] Saginaw 48609 4 Main 555/555/5555 Saginaw 48609

69. 2NF - users id email password active hire_date office_name office_phone office_city office_zip 1 [email protected] hash1 1 1/1/2024 Main Office 555-555-5555 Saginaw 48609 2 [email protected] NULL 1 8/11/2024 main office 5555555555 Saginaw 48609 3 [email protected] hash3 1 May 11th, 23 Man office [phone] Saginaw 48609 4 [email protected] hash4 1 Tuesday Main 555/555/5555 Saginaw 48609

70. 2NF - users id email password active hire_date office_name office_phone office_city office_zip office_id 1 [email protected] hash1 1 1/1/2024 Main Office 555-555-5555 Saginaw 48609 1 2 [email protected] NULL 1 8/11/2024 main office 5555555555 Saginaw 48609 2 3 [email protected] hash3 1 May 11th, 23 Man office [phone] Saginaw 48609 3 4 [email protected] hash4 1 Tuesday Main 555/555/5555 Saginaw 48609 4

71. 2NF - users id email password active hire_date office_id 1 [email protected] hash1 1 1/1/2024 1 2 [email protected] NULL 1 8/11/2024 2 3 [email protected] hash3 1 May 11th, 23 3 4 [email protected] hash4 1 Tuesday 4

72. Third Normal Form (3NF)

73. Third Normal Form (3NF) Is already in 2NF It contains columns that are non-transitively dependent on the primary key

74. 3NF - offices id name phone city zip 1 Main Office 555-555-5555 Saginaw 48609 2 main office 5555555555 Saginaw 48609 3 Man office [phone] Saginaw 48609 4 Main 555/555/5555 Saginaw 48609

75. 3NF - zips id city 48609 Saginaw 48640 Midland 48642 Midland 48901 Lansing

76. 3NF - zips id city state 48609 Saginaw MI 48640 Midland MI 48642 Midland MI 48901 Lansing MI

77. Old Table Structure

78. New Table Structure

79. Use The Database Engine to Keep Data Clean

80. Why?

81. Why? $validated = $request ->validate([ "email" => "required|unique:users|max:255" , ]);

82. mysql> insert into users (password) values (“just a password?"); Query OK, 1 row affected (0.01 sec)

83. mysql> insert into users (password) values (“just a password?"); Query OK, 1 row affected (0.01 sec)

84. Garbage In -> Garbage Out

85. Garbage Data -> Bugs

86. To Prevent Bugs: Make The Database Work For Us

87. id email password active hire_date office_id 1 [email protected] hash1 1 1/1/2024 1 2 [email protected] NULL 1 8/11/2024 2 3 [email protected] hash3 1 May 11th, 23 3 4 [email protected] hash4 1 Tuesday 4 5 Hash12 2 2024-04-01 1000

88. Use Correct Column Types

89. Use Correct Column Types id email password active hire_date office_id 1 [email protected] hash1 1 1/1/2024 1 2 [email protected] NULL 1 8/11/2024 2 3 [email protected] hash3 1 May 11th, 23 3 4 [email protected] hash4 1 Tuesday 4 5 Hash12 2 2024-04-01 1000

90. Use Correct Column Types Numeric: INT, TINYINT, BIGINT, FLOAT, REAL, etc. Date/Time: DATE, TIME, DATETIME, etc. String: CHAR, VARCHAR, TEXT, etc. Binary data types such as: BLOB, etc.

91. Use Correct Column Types create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date char ( 255 ), office_id int );

92. Use Correct Column Types create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int );

93. Use Correct Column Types mysql> insert into users (hire_date) values ("tuesday"); ERROR 1292 (22007): Incorrect date value: 'tuesday' for column 'hire_date' at row 1 mysql> insert into users (hire_date) values ("May 11th, 23"); ERROR 1292 (22007): Incorrect date value: 'May 11th, 23' for column 'hire_date' at row 1

94. Use NOT NULL for Required Fields

95. Use NOT NULL for Required Fields mysql> insert into users (password) values (“just a password?"); Query OK, 1 row affected (0.01 sec)

96. Use NOT NULL for Required Fields create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int );

97. Use NOT NULL for Required Fields create table users ( id int primary key auto_increment , email char ( 255 ) not null , password char ( 255 ) not null , active tinyint ( 1 ) not null , hire_date date not null, office_id int not null );

98. Use NOT NULL for Required Fields mysql> insert into users (password) values (“just a password?”); ERROR 1364 (HY000): Field ‘email' doesn't have a default value

99. Use NOT NULL for Required Fields mysql> insert into users (password) values (“just a password?”); ERROR 1364 (HY000): Field ‘email' doesn't have a default value

100. Use NOT NULL for Required Fields mysql> insert into users (email, password) values (“s@s”, "just a password?"); ERROR 1364 (HY000): Field 'active' doesn't have a default value

101. Use UNIQUE for Unique Values

102. Use UNIQUE for Unique Values mysql> insert into users (email) values ("[email protected]"); Query OK, 1 row affected (0.01 sec) mysql> insert into users (email) values ("[email protected]"); Query OK, 1 row affected (0.02 sec)

103. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int ); Use UNIQUE for Unique Values

104. Use UNIQUE for Unique Values create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int, unique (email) );

105. Use UNIQUE for Unique Values mysql> insert into users (email) values ("[email protected]"); ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'users.email'

106. Use UNIQUE for Unique Values create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int, unique (email, office_id) );

107. Use Foreign Keys For References To Other Tables

108. Use Foreign Keys For References To Other Tables id name phone city zip_id 1 Main Office 555-555-5555 Saginaw 48609 2 main office 5555555555 Saginaw 48609 3 Man office [phone] Saginaw 48609 4 Main 555/555/5555 Saginaw 48609

109. Use Foreign Keys For References To Other Tables id name phone city zip_id 1 Main Office 555-555-5555 Saginaw 48609 2 main office 5555555555 Saginaw 48609 3 Man office [phone] Saginaw 48609

110. Use Foreign Keys For References To Other Tables id name phone city zip_id 1 Main Office 555-555-5555 Saginaw 48609 2 main office 5555555555 Saginaw 48609

111. Use Foreign Keys For References To Other Tables id name phone city zip_id 1 Main Office 555-555-5555 Saginaw 48609

112. Constraints - Foreign Keys

113. select count(*) from users; select name, email, offices.name from users inner join offices on users.office_id = offices.id;

114. Orphaned Rows

115. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active tinyint ( 1 ), hire_date date , office_id int );

116. mysql> insert into users (email, office_id) values ("[email protected]", 1000000); Query OK, 1 row affected (0.03 sec)

117. mysql> insert into users (email, office_id ) values ("[email protected]", 1000000 ); Query OK, 1 row affected (0.03 sec)

118. Foreign Key Constraints

119. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int, ); Foreign Key Constraints create table offices ( id int primary key auto_increment , name char ( 255 ), phone char ( 255 ), zip_id int );

120. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int, foreign key ); Foreign Key Constraints create table offices ( id int primary key auto_increment , name char ( 255 ), phone char ( 255 ), zip_id int );

121. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int, foreign key (office_id) ); Foreign Key Constraints create table offices ( id int primary key auto_increment , name char ( 255 ), phone char ( 255 ), zip_id int );

122. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int, foreign key (office_id) references offices(id) ); Foreign Key Constraints create table offices ( id int primary key auto_increment , name char ( 255 ), phone char ( 255 ), zip_id int );

123. Foreign Key Constraints mysql> insert into users (email, office_id) values ("[email protected]", 1000000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`databasetalk`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`))

124. Foreign Key Constraints mysql> insert into users (email, office_id) values ("[email protected]", 1000000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`databasetalk`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`))

125. Foreign Key Constraints mysql> insert into users (email, office_id ) values ("[email protected]", 1 ); Query OK, 1 row affected (0.01 sec)

126. Foreign Key Constraints mysql> delete from offices where id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`databasetalk`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`))

127. Foreign Key Constraints mysql> delete from offices where id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`databasetalk`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`))

128. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int ); Foreign Key Constraints create table user_password_histories ( id int primary key auto_increment , user_id int , password char ( 255 ), foreign key (user_id) references users(id) on delete cascade );

129. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int ); Foreign Key Constraints create table user_password_histories ( id int primary key auto_increment , user_id int , password char ( 255 ), foreign key (user_id) references users(id) on delete cascade );

130. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int ); Foreign Key Constraints create table user_password_histories ( id int primary key auto_increment , user_id int , password char ( 255 ), foreign key (user_id) references users(id) on delete cascade );

131. Foreign Key Constraints mysql> insert into user_password_histories (user_id, password) values (2, "test1"); Query OK, 1 row affected (0.01 sec) mysql> insert into user_password_histories (user_id, password) values (2, "test2"); Query OK, 1 row affected (0.01 sec)

132. Foreign Key Constraints mysql> delete from users where id = 2; Query OK, 1 row affected (0.01 sec) mysql> select * from user_password_histories where user_id = 2; Empty set (0.00 sec)

133. Foreign Key Constraints mysql> delete from users where id = 2; Query OK, 1 row affected (0.01 sec) mysql> select * from user_password_histories where user_id = 2; Empty set (0.00 sec)

134. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int ); Foreign Key Constraints create table user_password_histories ( id int primary key auto_increment , user_id int , password char ( 255 ), foreign key (user_id) references users(id) on delete cascade );

135. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int ); Foreign Key Constraints create table user_password_histories ( id int primary key auto_increment , user_id int , password char ( 255 ), foreign key (user_id) references users(id) on delete set null );

136. Downsides to Constraints

137. Performance

138. Use Triggers For Complex Requirements

139. Use Triggers For Complex Requirements id email password active hire_date office_id 1 [email protected] hash1 1 1/1/2024 1 2 [email protected] NULL 1 8/11/2024 2 3 [email protected] hash3 1 May 11th, 23 3 4 [email protected] hash4 1 Tuesday 4 5 Hash12 2 2024-04-01 1000

140. Use Triggers For Complex Requirements Triggers add additional power to DB Operate based on create, update, or delete

141. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_valid_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

142. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_validate_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

143. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_validate_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

144. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_validate_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

145. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_validate_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

146. Use Triggers For Complex Requirements CREATE TRIGGER users_before_insert_validate_active BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

147. Use Triggers For Complex Requirements CREATE TRIGGER users_before_update_validate_active BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.active < || NEW.active > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'active must be 0 or 1' ; END IF ; END ;

148. Use Triggers For Complex Requirements mysql> insert into users (active) values (2); ERROR 1644 (45000): active must be 0 or 1

149. Use Triggers For Complex Requirements mysql> insert into users (active) values (2); ERROR 1644 (45000): active must be 0 or 1

150. type = ? Require fields 1,2,3 1 Require fields 2,5,11 2

151. Downsides to Triggers

152. Performance

153. “Magic”

154. Proactively Add Indexes to Keep Queries Performant

155. Indexes in Databases

156. select * from users where hire_date = "2023-12-03"

157. Indexes in Databases

158. Indexes in Databases

159. Indexes in Databases 2023-12-03 Index: hire_date 2023-12-04 2023-12-05 2023-12-06

160. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int , );

161. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int , index (hire_date), );

162. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int , index (hire_date), index (active), );

163. create table users ( id int primary key auto_increment , email char ( 255 ), password char ( 255 ), active char ( 255 ), hire_date date , office_id int , index (hire_date), index (active), index (hire_date, active) );

164. Start Out Simple

165. Start Out Simple We “automatically” create indexes for: All foreign key relationships Any column that’s searched

166. Start Out Simple Every month or so run database tools to find missing indexes set global log_queries_not_using_indexes = “On”; mysqldumpslow to check results after an hour, a day, and a week Determine IF we feel it’s necessary

167. Denormalize Your Database For Speed

168. Denormalize Your Database For Speed “[D]enormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.” -“Denormalization” on Wikipedia

169. Denormalize Your Database For Speed Office Jan Feb March … Nov Dec Charges (USD) 10 7 8 … 8 7 Credits (USD) 2 … 1 1 Costs (USD) 2 2 2 … 2 2 Profit 6 5 6 … 5 4

170. Denormalize Your Database For Speed Office Jan Feb March … Nov Dec Charges (USD) 10 7 8 … 8 7 Credits (USD) 2 … 1 1 Costs (USD) 2 2 2 … 2 2 Profit 6 5 6 … 5 4

171. Denormalize Your Database For Speed What we found: 10s of millions charges 100s of thousand credits 10s of thousand costs

172. Methods of Denormalization Pre-joining Table Splitting Adding derived and redundant columns Mirrored tables

173. Pre-joining create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , created_at datetime );

174. Pre-joining create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , office_name not null char ( 255 ), created_at datetime );

175. Table Splitting create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , created_at datetime ); create table charges_office_1 ( id int primary key auto_increment , amount not null int , office_id not null int , created_at datetime ); create table charges_office_2 ( id int primary key auto_increment , amount not null int , office_id not null int , created_at datetime ); create table charges_office_3 ( id int primary key auto_increment , amount not null int , office_id not null int , created_at datetime );

176. Table Splitting create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , description text , notes text , file_upload_1 blob , file_upload_2 blob , file_upload_3 blob , file_upload_4 blob , file_upload_5 blob , created_at datetime ); create table charges_billing ( id int primary key , amount not null int , office_id not null int , created_at datetime ); create table charges_support ( id int primary key , description text , notes text , file_upload_1 blob , file_upload_2 blob , file_upload_3 blob , file_upload_4 blob , file_upload_5 blob , created_at datetime );

177. Adding Derived and Redundant Columns create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , invoice_month int not null, created_at datetime );

178. Adding Derived and Redundant Columns create table charges ( id int primary key auto_increment , amount not null int , office_id not null int , invoice_month int not null , created_at datetime );

179. Mirrored Tables Office Jan Feb March … Nov Dec Charges (USD) 10 7 8 … 8 7 Credits (USD) 2 … 1 1 Costs (USD) 2 2 2 … 2 2 Profit 6 5 6 … 5 4

180. Mirrored Tables create table monthly_stats ( id int primary key auto_increment , month_and_year varchar ( 255 ) not null , charges int not null , credits int not null , costs int not null );

181. Mirrored Tables Office Jan Feb March … Nov Dec Charges (USD) 10 7 8 … 8 7 Credits (USD) 2 … 1 1 Costs (USD) 2 2 2 … 2 2 Profit 6 5 6 … 5 4

182. Generating Denormalize Data Delayed Generate using cron job (every day or hour) “Instantly” Internal logic inside the application (event sourcing) Use a trigger inside the db

183. What You Need to Know

184. What You Need to Know Backup Your Database and Test It Normalize Your Database For Data Deduplication Use The Database Engine to Keep Data Clean Proactively Add Indexes to Keep Queries Performant Denormalize Your Database For Speed

185. What You Need to Know The table contains a unique identifier, also called the primary key, that is used to identify the row. Each column contains atomic values (values that can not be broken down) All the non-key columns are dependent on the primary key of the table It contains columns that are non-transitively dependent on the primary key

186. What You Need to Know Make the DB Work With You Correct Column Types NOT NULL for Required Fields UNIQUE for Unique Values Foreign Keys For References To Other Tables Triggers For Complex Requirements

187. What You Need to Know Use indexes on commonly searched columns Start simple

188. What You Need to Know Pre-joining Table Splitting Adding derived and redundant columns Mirrored tables

189. Thank The Sponsors

190. Thank The Organizers

191. Thank The Speakers

192. Questions/Follow Me Questions? Please rate the talk https://tinyurl.com/todo