Database Design for Beginners

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.

F74253f4a099258870157426b4cdb2dc?s=128

David Copeland

May 01, 2019
Tweet

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

  13. None
  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.
  16. None
  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?
  26. None
  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?

  126. None
  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
  131. None
  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