Database Design for Beginners

Rails’ migrations were a revelation when Rails came out, as it provided a way to manage your database schema, but also included many wonderful defaults that made all Rails developers pretty good at database schema design! But these defaults are just the beginning. Properly modeling your database can bring many benefits, such as fewer bugs, more reliable insights about your users or business, and developer understanding. We’ll talk about what makes a good database design by understanding the concept of normalization and why a properly normalized database yields benefits.

Transcript

1. DATABASE DESIGN FOR BEGINNERS DAVE COPELAND @DAVETRON5000
2. THOUGHT EXPERIMENT
3. YOUR APP’S SOURCE CODE GOES AWAY FOREVER WITH NO BACKUP POSSIBLE
4. YOUR DATABASE GOES AWAY FOREVER WITH NO BACKUPS
5. DATA > SOURCE CODE
6. DATABASE DESIGN FOR BEGINNERS DAVE COPELAND @DAVETRON5000
7. RailsConf2019_rails6 DISCOUNT CODE:
8. WHAT IS A “DATABASE”?
9. OLTP ONLINE TRANSACTION PROCESSING
10. AN OLTP IS THE SOURCE OF TRUTH—IT RECORDS FACTS ABOUT THE WORLD
11. DATABASE DESIGN IS HOW WE ENSURE OUR DATABASE IS THE SOURCE OF TRUTH
12. WHAT FACTS DO WE WANT TO RECORD? STEP 1
14. PROFESSIONAL WRESTLING A DOMAIN EVERYONE CAN LOVE • A wrestler may have a finishing move e.g. The Rock’s finishing move is the Rock Bottom • A wrestler might wrestle on a show e.g. The Rock wrestles on Smackdown • A wrestler can be cleared to compete on one or more matches of a show e.g. The Rock wrestlers on the main event of Smackdown, but Zack Ryder wrestlers on the opener. • A show may have a title belt that is defended on that show e.g. On Smackdown, the WWE Championship is defended.
15. PROFESSIONAL WRESTLING A DOMAIN EVERYONE CAN LOVE • A wrestler may have a finishing move e.g. The Rock’s finishing move is the Rock Bottom • A wrestler might wrestle on a show e.g. The Rock wrestles on Smackdown • A wrestler can be cleared to compete on one or more matches of a show e.g. The Rock wrestlers on the main event of Smackdown, but Zack Ryder wrestlers on the opener. • A show may have a title belt that is defended on that show e.g. On Smackdown, the WWE Championship is defended.
17. WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE
18. WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH MATCHES NAME
19. WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH WRESTLER CLEARANCES WRESTLER SHOW MATCH MATCHES NAME
20. ANOMALIES WHEN THE DATA MODEL PREVENTS THE STORAGE OF CERTAIN FACTS, ALLOWS AMBIGUITY TO EXIST, OR REQUIRES DELETING ONE FACT TO DELETE ANOTHER
21. NORMALIZATION IS THE PROCESS BY WHICH WE REMOVE ANOMALIES FROM OUR DESIGN
22. NORMAL FORMS ARE MATHEMATICAL TRUTHS ABOUT A DATA MODEL THAT PROVE WHAT SORTS OF ANOMALIES HAVE BEEN REMOVED
23. PROFESSIONAL WRESTLING A DOMAIN EVERYONE CAN LOVE • A wrestler may have a finishing move. • A wrestler might wrestle on a show. • A wrestler can be cleared to compete on one or more matches of a show. • A show may have a title belt that is defended on that show.
24. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW, Smackdown Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event, Opener Kevin Owens Stunner RAW Universal Title Main Event
25. THIS IS NOT A GOOD DATABASE DESIGN. HOW DO WE KNOW THAT?
27. WHATEVER STORE ANYTHING
28. WHATEVER RELATION (FIRST NORMAL FORM) STORE ANYTHING KNOWN ANOMALIES
29. WHATEVER RELATION (FIRST NORMAL FORM) NORMALIZED (BOYCE-CODD NORMAL FORM) STORE ANYTHING KNOWN ANOMALIES MANY FEWER ANOMALIES
30. WHAT IS A RELATION?
31. WHAT IS A RELATION? • A relation requires: • No duplicate rows • Each field has a single value • Fields in the same column are of the same type
32. A NOTE ABOUT TYPES • Think about what are the allowed values - likely not strings or numbers • NULL is not a value, therefore NULL is not an allowed value, therefore no field can allow NULL • Remember, this is a design, not an implementation.
33. CREATE RELATIONS STEP 2 STEP 1
 WHAT FACTS DO WE WANT TO RECORD?
34. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW, Smackdown Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event, Opener Kevin Owens Stunner RAW Universal Title Main Event
35. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW, Smackdown Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event, Opener Kevin Owens Stunner RAW Universal Title Main Event
36. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW, Smackdown Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event, Opener Kevin Owens Stunner RAW Universal Title Main Event
37. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW, Smackdown Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event, Opener Kevin Owens Stunner RAW Universal Title Main Event
38. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event
39. FIRST NORMAL FORM NO DUPLICATE ROWS, EACH VALUE IS A SINGLE VALUE OF THE RIGHT TYPE (E.G. NO MULTIPLE VALUES)
40. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Million Dollar Dream Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event Kenny Omega One-Winged Angel ??? ??? ???
41. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Million Dollar Dream Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event Kenny Omega One-Winged Angel ??? ??? ???
42. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Million Dollar Dream Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event Kenny Omega One-Winged Angel ??? ??? ???
43. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Million Dollar Dream Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event Kenny Omega One-Winged Angel ??? ??? ???
44. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Million Dollar Dream Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event Kenny Omega One-Winged Angel ??? ??? ???
45. BECAUSE WE ARE IN FIRST NORMAL FORM, I.E. A RELATION, WE CAN SHOW WHY THIS IS FLAWED AND FIX IT WITHOUT A LOT OF THINKING!
46. …IF WE CAPTURE THE BUSINESS RULES AS FUNCTIONAL DEPENDENCIES AND KEYS
47. FUNCTIONAL DEPENDENCIES
48. FUNCTIONAL DEPENDENCIES • A FUNCTIONAL DEPENDENCY is when the value of a column unambiguously implies the value of another column, based on business rules.
49. FUNCTIONAL DEPENDENCIES • A FUNCTIONAL DEPENDENCY is when the value of a column unambiguously implies the value of another column, based on business rules. • A wrestler may only have a single finishing move, therefore: • WRESTLER implies FINISHING MOVE • A show may have a single title defended on it, therefore: • SHOW implies TITLE
50. NOT FUNCTIONAL DEPENDENCIES • Note that because a SHOW can have more than one MATCH, SHOW does not imply MATCH, so no functional dependency there. • FINISHING MOVE does not imply WRESTLER, because it’s allowed for many wrestlers to have the same finishing move.
51. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener The Rock Rock Bottom Smackdown WWE Championship Main Event Kevin Owens Stunner RAW Universal Championship Main Event ALTHOUGH OUR FUNCTIONAL DEPENDENCIES ARE SATISFIED BY THE DATA, THE DATA MODEL DOESN’T REQUIRE IT
52. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener The Rock Rock Bottom Smackdown WWE Championship Main Event Kevin Owens Stunner RAW Universal Championship Main Event ALTHOUGH OUR FUNCTIONAL DEPENDENCIES ARE SATISFIED BY THE DATA, THE DATA MODEL DOESN’T REQUIRE IT PEOPLE’S ELBOW
53. OUR DESIGN ALLOWS A QUERY BASED ON WRESTLER TO RETURN AN AMBIGUOUS RESULT FOR FINISHING MOVE THUS, IT IS OPEN TO ANOMALIES, AND THEREFORE FLAWED
54. THIS WILL BECOME RELEVANT IN A MOMENT, ONCE WE LEARN ABOUT KEYS
55. KEYS
56. KEYS • A key is a subset of columns that is uniquely identifies a row • i.e. no more than one row with the same set of values for the key
57. KEYS • A key is a subset of columns that is uniquely identifies a row • i.e. no more than one row with the same set of values for the key • A relation can have many keys, but always has at least one
58. KEYS • A key is a subset of columns that is uniquely identifies a row • i.e. no more than one row with the same set of values for the key • A relation can have many keys, but always has at least one • all columns form a key since there can be no duplicate rows
59. KEYS • A key is a subset of columns that is uniquely identifies a row • i.e. no more than one row with the same set of values for the key • A relation can have many keys, but always has at least one • all columns form a key since there can be no duplicate rows • business rules reveal other keys
60. KEYS BASED ON BUSINESS RULES
61. KEYS BASED ON BUSINESS RULES • According to our business rules, a wrestler cannot wrestle the same match on the same show more than once, therefore: • { WRESTLER, SHOW, MATCH } is a key
62. KEYS BASED ON BUSINESS RULES • According to our business rules, a wrestler cannot wrestle the same match on the same show more than once, therefore: • { WRESTLER, SHOW, MATCH } is a key • Meaning, if we have a WRESTLER, SHOW, and MATCH, we can use that to identify at most one row in our table, i.e. this produces an unambiguous answer.
63. KEYS BASED ON BUSINESS RULES • According to our business rules, a wrestler cannot wrestle the same match on the same show more than once, therefore: • { WRESTLER, SHOW, MATCH } is a key • Meaning, if we have a WRESTLER, SHOW, and MATCH, we can use that to identify at most one row in our table, i.e. this produces an unambiguous answer. • i.e. there can be no more than one row with the same values for WRESTLER, SHOW, and MATCH
64. WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event OUR DATA SATISFY THE KEY. THE DATA MODEL SIMPLY NEEDS TO STATE WHAT THE KEYS ARE FOR IT TO SATISFY THE KEY (WE’LL SEE HOW TO ENFORCE THIS IN A REAL DATABASE LATER ON)
65. IDENTIFY FUNCTIONAL DEPENDENCIES AND KEYS STEP 3 STEP 1
 WHAT FACTS DO WE WANT TO RECORD? STEP 2
 CREATE RELATIONS
66. REMINDER: KEYS AND FUNCTIONAL DEPENDENCIES ARE THE BUSINESS RULES. THEY CANNOT BE MATHEMATICALLY DERIVED BASED ON THE DATA
67. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES
68. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES To get unambiguous information: we want a query that returns 0 or 1 rows → thus we must use a key to get that.
69. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES To get unambiguous information: we want a query that returns 0 or 1 rows → thus we must use a key to get that. Our Functional Dependency { WRESTLER } → { FINISHING MOVE } says we should get an unambiguous FINISHING MOVE from just WRESTLER → however WRESTLER is not a key → meaning a query based on WRESTLER is might return ambiguous results
70. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES To get unambiguous information: we want a query that returns 0 or 1 rows → thus we must use a key to get that. Our Functional Dependency { WRESTLER } → { FINISHING MOVE } says we should get an unambiguous FINISHING MOVE from just WRESTLER → however WRESTLER is not a key → meaning a query based on WRESTLER is might return ambiguous results
71. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES To get unambiguous information: we want a query that returns 0 or 1 rows → thus we must use a key to get that. Our Functional Dependency { WRESTLER } → { FINISHING MOVE } says we should get an unambiguous FINISHING MOVE from just WRESTLER → however WRESTLER is not a key → meaning a query based on WRESTLER is might return ambiguous results ∴ Our database allows storing data that violates our business rules
72. OUR DESIGN DOES NOT SATISFY OUR FUNCTIONAL DEPENDENCIES IT DOES NOT MEET THE BUSINESS RULES
73. IF OUR DESIGN WAS SUCH THAT WRESTLER WAS A KEY OF SOME TABLE, FUNCTIONAL DEPENDENCIES WOULD ALWAYS TO BE SATISFIED
74. EXTRACT NEW TABLES STEP 4 STEP 1
 WHAT FACTS DO WE WANT TO RECORD? STEP 2
 WHAT ARE THE TYPES? STEP 3
 IDENTIFY FUNCTIONAL DEPENDENCIES AND KEYS
75. MAKE A NEW TABLE WHERE THE KEY IS THE VALUE ON THE LEFT SIDE OF THE FUNCTIONAL DEPENDENCY WRESTLER
76. ADD THE DEPENDENT COLUMNS TO THAT TABLE WRESTLER FINISHING MOVE
77. REMOVE THE DEPENDENT COLUMNS FROM THE ORIGINAL TABLE WRESTLER FINISHING MOVE WRESTLER SHOW TITLE MATCH
78. HEATH’S THEOREM ALSO SAYS WE HAVE NOT LOST DATA WRESTLER FINISHING MOVE The Rock Rock Bottom Stone Cold Steve Austin Stunner Kevin Owens Stunner WRESTLER SHOW TITLE MATCH The Rock RAW Universal Championship Main Event The Rock Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Opener Kevin Owens RAW Universal Championship Main Event
79. HEATH’S THEOREM ALSO SAYS WE HAVE NOT LOST DATA WRESTLER FINISHING MOVE The Rock Rock Bottom Stone Cold Steve Austin Stunner Kevin Owens Stunner WRESTLER SHOW TITLE MATCH The Rock RAW Universal Championship Main Event The Rock Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Opener Kevin Owens RAW Universal Championship Main Event
80. HEATH’S THEOREM ALSO SAYS WE HAVE NOT LOST DATA WRESTLER FINISHING MOVE The Rock Rock Bottom Stone Cold Steve Austin Stunner Kevin Owens Stunner WRESTLER SHOW TITLE MATCH The Rock RAW Universal Championship Main Event The Rock Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Main Event Stone Cold Steve Austin Smackdown WWE Championship Opener Kevin Owens RAW Universal Championship Main Event WRESTLER FINISHING MOVE SHOW TITLE MATCH The Rock Rock Bottom RAW Universal Championship Main Event The Rock Rock Bottom Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Main Event Stone Cold Steve Austin Stunner Smackdown WWE Championship Opener Kevin Owens Stunner RAW Universal Championship Main Event
81. WRESTLER MATCHES WRESTLER SHOW MATCH The Rock RAW Main Event The Rock Smackdown Main Event Stone Cold Steve Austin Smackdown Main Event Stone Cold Steve Austin Smackdown Opener Kevin Owens RAW Main Event FINISHING MOVES WRESTLER FINISHING MOVE The Rock Rock Bottom Stone Cold Steve Austin Stunner Kevin Owens Stunner TITLES SHOW TITLE RAW WWE Championship Smackdown Universal Championship
82. WRESTLER MATCHES WRESTLER SHOW MATCH The Rock RAW Main Event The Rock Smackdown Main Event Stone Cold Steve Austin Smackdown Main Event Stone Cold Steve Austin Smackdown Opener Kevin Owens RAW Main Event FINISHING MOVES WRESTLER FINISHING MOVE The Rock Rock Bottom Stone Cold Steve Austin Stunner Kevin Owens Stunner TITLES SHOW TITLE RAW WWE Championship Smackdown Universal Championship
83. THESE TABLES ARE IN BOYCE-CODD NORMAL FORM, MEANING THEY HAVE NO ANOMALIES BASED ON OUR FUNCTIONAL DEPENDENCIES
84. MANY ANOMALIES ADDRESSED WRESTLER MATCHES WRESTLER SHOW MATCH The Rock RAW Main Event The Rock Smackdown Main Event Stone Cold Steve Austin Smackdown Main Event Stone Cold Steve Austin Smackdown Opener Kevin Owens RAW Main Event Zack Ryder Superstars Main Event Ricochet RAW Main Event FINISHING MOVES WRESTLER FINISHING MOVE The Rock People’s Elbow Stone Cold Steve Austin Stunner Kevin Owens Stunner Kenny Omega One-Winged Angel Zack Ryder Rough Ryder TITLES SHOW TITLE RAW Heavyweight Championship Smackdown Universal Championship
85. THIS PROCESS IS CALLED HEATH’S THEOREM, WHICH PROVES WE REMOVED ANOMALIES AND DIDN’T LOSE DATA
86. HOW DID WE DO THIS?
87. HOW DID WE DO THIS? 1. What facts do we want to record?
88. HOW DID WE DO THIS? 1. What facts do we want to record? 2. Create Relations
89. HOW DID WE DO THIS? 1. What facts do we want to record? 2. Create Relations 3. Identify Functional Dependencies and Keys
90. HOW DID WE DO THIS? 1. What facts do we want to record? 2. Create Relations 3. Identify Functional Dependencies and Keys 4. Extract New Tables/Apply Heath’s Theorem: 1. Anything functional dependent on only part of a key, make a new table 2. That table’s key is the the left side of the dependency 3. The table’s columns are the right size 4. Remove the right side columns from the original table
91. OK, BUT WHERE ARE THE REAL PRIMARY KEYS? WHAT OF ID?
92. PRIMARY KEYS
93. PRIMARY KEYS • The keys we saw like { WRESTLER, SHOW, MATCH } are primary keys - they uniquely identify the row.
94. PRIMARY KEYS • The keys we saw like { WRESTLER, SHOW, MATCH } are primary keys - they uniquely identify the row. • They are called natural keys or sometimes business keys. • They are handy because they represent facts and are easily understood.
95. PRIMARY KEYS • The keys we saw like { WRESTLER, SHOW, MATCH } are primary keys - they uniquely identify the row. • They are called natural keys or sometimes business keys. • They are handy because they represent facts and are easily understood. • But…what if we rename a show or a wrestler? We have to change the key values everywhere. It’s messy.
96. PRIMARY KEYS • The keys we saw like { WRESTLER, SHOW, MATCH } are primary keys - they uniquely identify the row. • They are called natural keys or sometimes business keys. • They are handy because they represent facts and are easily understood. • But…what if we rename a show or a wrestler? We have to change the key values everywhere. It’s messy. • The ID field Rails makes is called a synthetic key or surrogate key.
97. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE
98. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE KEY
99. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE ALSO A KEY
100. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH WRESTLER WRESTLER ID SHOW SHOW ID MATCH The Rock 1 RAW 1 Main Event The Rock 2 Smackdown 2 Main Event Stone Cold Steve Austin 3 Smackdown 3 Main Event Stone Cold Steve Austin 4 Smackdown 4 Opener Kevin Owens 5 RAW 5 Main Event
101. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE
102. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH
103. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH SHOWS SHOW ID WRESTLERS WRESTLER ID
104. WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH SHOWS SHOW ID SHOW WRESTLERS WRESTLER ID WRESTLER
105. WRESTLER SHOWS WRESTLER ID SHOW ID MATCH SHOWS SHOW ID SHOW WRESTLERS WRESTLER ID WRESTLER BOYCE-CODD NORMAL FORM I.E. FREE OF ANOMALIES RELATED TO FUNCTIONAL DEPENDENCIES! KEY KEY KEY
106. WRESTLER SHOWS WRESTLER_ID SHOW_ID MATCH SHOWS SHOW ID SHOW WRESTLERS WRESTLER ID WRESTLER FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE
107. WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH WRESTLER CLEARANCES WRESTLER SHOW MATCH MATCHES NAME
108. HOWEVER: WE’VE BEEN TALKING ABOUT THE LOGICAL MODEL. bin/rails g migration IS ABOUT THE PHYSICAL MODEL
109. LOGIC TO PHYSICAL
110. LOGIC TO PHYSICAL • The tables, i.e. migrations are the implementation of the design
111. LOGIC TO PHYSICAL • The tables, i.e. migrations are the implementation of the design • Primary concerns: • Enforcing keys (remember, there are business keys still in effect even if using synthetic keys) • Enforcing associations • Enforcing types
112. KEYS CAN BE ENFORCED WITH UNIQUE INDEXES add_index :wrestlers, :name, unique: true
113. create_table :wrestler_shows do |t| t.references :wrestler, null: false, foreign_key: true t.references :show, null: false, foreign_key: true t.string :match, null: false end add_index :wrestler_shows, [ :wrestler_id, :show_id, :match ], unique: true
114. ASSOCIATIONS CAN BE ENFORCED WITH FOREIGN KEY CONSTRAINTS t.references :shows, null: false, foreign_key: true
115. create_table :shows do |t| t.string :name, null: false end add_index :shows, :name, unique: true create_table :show_titles do |t| t.string :name, null: false t.references :show, null: false, foreign_key: true end
116. TYPES ARE HARDER AND POTENTIALLY IMPOSSIBLE TO GET PERFECT DEPENDING ON YOUR DATABASE
117. create_table :wrestlers do |t| t.string :name, null: false end add_index :wrestlers, :name, unique: true
118. ON TYPES
119. ON TYPES • These are the same wrestler: The Rock, THE ROCK, The Rock
120. ON TYPES • These are the same wrestler: The Rock, THE ROCK, The Rock • This is in no way the name of a wrestler: “ “
121. ON TYPES • These are the same wrestler: The Rock, THE ROCK, The Rock • This is in no way the name of a wrestler: “ “ • Trade-off time: • How likely is bad data to be input? • How bad is it if it happens?
122. create_table :wrestlers do |t| t.string :name, null: false end add_index :wrestlers, ”lower(name)”, unique: true
123. execute %{ ALTER TABLE wrestlers ADD CONSTRAINT no_blank_wrestler_names CHECK ( TRIM(name) <> '' )
 }
124. class Wrestler < ApplicationRecord validates :name, presence: true, uniqueness: { case_sensitive: false } end
125. WHAT ABOUT NULL/nil?
127. create_table :finishing_moves do |t| t.string :name, null: false t.references :wrestler, null: false, foreign_key: true end
128. create_table :finishing_moves do |t| t.string :name, null: false t.references :wrestler, null: false, foreign_key: true end # In our code wrestler.finishing_move.name # undefined method `name’ # for NilClass
129. create_table :finishing_moves do |t| t.string :name, null: false t.references :wrestler, null: false, foreign_key: true end # In our code wrestler.finishing_move.name # undefined method `name’ # for NilClass wrestler.finishing_move&.name
130. create_table :finishing_moves do |t| t.string :name, null: false t.references :wrestler, null: false, foreign_key: true end # In our code wrestler.finishing_move.name # undefined method `name’ # for NilClass wrestler.finishing_move&.name def finishing_move_name self.finishing_move&.name end
132. create_table :wrestlers do |t| t.string :name, null: false t.string :finishing_move, null: true,
133. create_table :wrestlers do |t| t.string :name, null: false t.string :finishing_move, null: true, comment: "null if the wrestler has no finishing move" end
134. GENERAL GUIDANCE • Create unique indexes for all business keys • Use foreign key constraints • Default to not-null • Comment nullable fields • Use database constraints if the downside of bad data is really bad, otherwise validations
135. CLOSING THOUGHTS • Boyce-Codd Normal form does not eliminate all anomalies, but it eliminates many! • When editing your model, edit your design, then flow through to the physical. • Start with business rules, i.e. functional dependencies and keys.
136. NEVER FORGET THAT YOUR DATA IS MORE IMPORTANT THAN YOUR APP - TREAT IT THAT WAY!
137. RailsConf2019_rails6 DAVE COPELAND @DAVETRON5000 DAVETRON5000.COM