Database Design for Beginners

Slide 1

Slide 1 text

DATABASE DESIGN FOR BEGINNERS DAVE COPELAND @DAVETRON5000

Slide 2

Slide 2 text

THOUGHT EXPERIMENT

Slide 3

Slide 3 text

YOUR APP’S SOURCE CODE GOES AWAY FOREVER WITH NO BACKUP POSSIBLE

Slide 4

Slide 4 text

YOUR DATABASE GOES AWAY FOREVER WITH NO BACKUPS

Slide 5

Slide 5 text

DATA > SOURCE CODE

Slide 6

Slide 6 text

DATABASE DESIGN FOR BEGINNERS DAVE COPELAND @DAVETRON5000

Slide 7

Slide 7 text

RailsConf2019_rails6 DISCOUNT CODE:

Slide 8

Slide 8 text

WHAT IS A “DATABASE”?

Slide 9

Slide 9 text

OLTP ONLINE TRANSACTION PROCESSING

Slide 10

Slide 10 text

AN OLTP IS THE SOURCE OF TRUTH—IT RECORDS FACTS ABOUT THE WORLD

Slide 11

Slide 11 text

DATABASE DESIGN IS HOW WE ENSURE OUR DATABASE IS THE SOURCE OF TRUTH

Slide 12

Slide 12 text

WHAT FACTS DO WE WANT TO RECORD? STEP 1

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

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.

Slide 15

Slide 15 text

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE

Slide 18

Slide 18 text

WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH MATCHES NAME

Slide 19

Slide 19 text

WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH WRESTLER CLEARANCES WRESTLER SHOW MATCH MATCHES NAME

Slide 20

Slide 20 text

ANOMALIES WHEN THE DATA MODEL PREVENTS THE STORAGE OF CERTAIN FACTS, ALLOWS AMBIGUITY TO EXIST, OR REQUIRES DELETING ONE FACT TO DELETE ANOTHER

Slide 21

Slide 21 text

NORMALIZATION IS THE PROCESS BY WHICH WE REMOVE ANOMALIES FROM OUR DESIGN

Slide 22

Slide 22 text

NORMAL FORMS ARE MATHEMATICAL TRUTHS ABOUT A DATA MODEL THAT PROVE WHAT SORTS OF ANOMALIES HAVE BEEN REMOVED

Slide 23

Slide 23 text

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.

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

THIS IS NOT A GOOD DATABASE DESIGN. HOW DO WE KNOW THAT?

Slide 26

Slide 26 text

No content

Slide 27

Slide 27 text

WHATEVER STORE ANYTHING

Slide 28

Slide 28 text

WHATEVER RELATION (FIRST NORMAL FORM) STORE ANYTHING KNOWN ANOMALIES

Slide 29

Slide 29 text

WHATEVER RELATION (FIRST NORMAL FORM) NORMALIZED (BOYCE-CODD NORMAL FORM) STORE ANYTHING KNOWN ANOMALIES MANY FEWER ANOMALIES

Slide 30

Slide 30 text

WHAT IS A RELATION?

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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.

Slide 33

Slide 33 text

CREATE RELATIONS STEP 2 STEP 1  WHAT FACTS DO WE WANT TO RECORD?

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

FIRST NORMAL FORM NO DUPLICATE ROWS, EACH VALUE IS A SINGLE VALUE OF THE RIGHT TYPE (E.G. NO MULTIPLE VALUES)

Slide 40

Slide 40 text

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 ??? ??? ???

Slide 41

Slide 41 text

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

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!

Slide 46

Slide 46 text

…IF WE CAPTURE THE BUSINESS RULES AS FUNCTIONAL DEPENDENCIES AND KEYS

Slide 47

Slide 47 text

FUNCTIONAL DEPENDENCIES

Slide 48

Slide 48 text

FUNCTIONAL DEPENDENCIES • A FUNCTIONAL DEPENDENCY is when the value of a column unambiguously implies the value of another column, based on business rules.

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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.

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

THIS WILL BECOME RELEVANT IN A MOMENT, ONCE WE LEARN ABOUT KEYS

Slide 55

Slide 55 text

KEYS

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

Slide 60

Slide 60 text

KEYS BASED ON BUSINESS RULES

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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.

Slide 63

Slide 63 text

Slide 64

Slide 64 text

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)

Slide 65

Slide 65 text

IDENTIFY FUNCTIONAL DEPENDENCIES AND KEYS STEP 3 STEP 1  WHAT FACTS DO WE WANT TO RECORD? STEP 2  CREATE RELATIONS

Slide 66

Slide 66 text

REMINDER: KEYS AND FUNCTIONAL DEPENDENCIES ARE THE BUSINESS RULES. THEY CANNOT BE MATHEMATICALLY DERIVED BASED ON THE DATA

Slide 67

Slide 67 text

IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES

Slide 68

Slide 68 text

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.

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

Slide 71

Slide 71 text

Slide 72

Slide 72 text

OUR DESIGN DOES NOT SATISFY OUR FUNCTIONAL DEPENDENCIES IT DOES NOT MEET THE BUSINESS RULES

Slide 73

Slide 73 text

IF OUR DESIGN WAS SUCH THAT WRESTLER WAS A KEY OF SOME TABLE, FUNCTIONAL DEPENDENCIES WOULD ALWAYS TO BE SATISFIED

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

MAKE A NEW TABLE WHERE THE KEY IS THE VALUE ON THE LEFT SIDE OF THE FUNCTIONAL DEPENDENCY WRESTLER

Slide 76

Slide 76 text

ADD THE DEPENDENT COLUMNS TO THAT TABLE WRESTLER FINISHING MOVE

Slide 77

Slide 77 text

REMOVE THE DEPENDENT COLUMNS FROM THE ORIGINAL TABLE WRESTLER FINISHING MOVE WRESTLER SHOW TITLE MATCH

Slide 78

Slide 78 text

Slide 79

Slide 79 text

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

Slide 83

Slide 83 text

THESE TABLES ARE IN BOYCE-CODD NORMAL FORM, MEANING THEY HAVE NO ANOMALIES BASED ON OUR FUNCTIONAL DEPENDENCIES

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

THIS PROCESS IS CALLED HEATH’S THEOREM, WHICH PROVES WE REMOVED ANOMALIES AND DIDN’T LOSE DATA

Slide 86

Slide 86 text

HOW DID WE DO THIS?

Slide 87

Slide 87 text

HOW DID WE DO THIS? 1. What facts do we want to record?

Slide 88

Slide 88 text

HOW DID WE DO THIS? 1. What facts do we want to record? 2. Create Relations

Slide 89

Slide 89 text

HOW DID WE DO THIS? 1. What facts do we want to record? 2. Create Relations 3. Identify Functional Dependencies and Keys

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

OK, BUT WHERE ARE THE REAL PRIMARY KEYS? WHAT OF ID?

Slide 92

Slide 92 text

PRIMARY KEYS

Slide 93

Slide 93 text

PRIMARY KEYS • The keys we saw like { WRESTLER, SHOW, MATCH } are primary keys - they uniquely identify the row.

Slide 94

Slide 94 text

Slide 95

Slide 95 text

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.

Slide 96

Slide 96 text

Slide 97

Slide 97 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE

Slide 98

Slide 98 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE KEY

Slide 99

Slide 99 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE ALSO A KEY

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH FINISHING MOVES WRESTLER ID FINISHING MOVE TITLES SHOW ID TITLE

Slide 102

Slide 102 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH

Slide 103

Slide 103 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH SHOWS SHOW ID WRESTLERS WRESTLER ID

Slide 104

Slide 104 text

WRESTLER SHOWS WRESTLER ID SHOW ID WRESTLER SHOW MATCH SHOWS SHOW ID SHOW WRESTLERS WRESTLER ID WRESTLER

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

WRESTLERS NAME FINISHING MOVE SHOWS NAME TITLE SHOW MATCHES SHOW MATCH WRESTLER CLEARANCES WRESTLER SHOW MATCH MATCHES NAME

Slide 108

Slide 108 text

HOWEVER: WE’VE BEEN TALKING ABOUT THE LOGICAL MODEL. bin/rails g migration IS ABOUT THE PHYSICAL MODEL

Slide 109

Slide 109 text

LOGIC TO PHYSICAL

Slide 110

Slide 110 text

LOGIC TO PHYSICAL • The tables, i.e. migrations are the implementation of the design

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

KEYS CAN BE ENFORCED WITH UNIQUE INDEXES add_index :wrestlers, :name, unique: true

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

ASSOCIATIONS CAN BE ENFORCED WITH FOREIGN KEY CONSTRAINTS t.references :shows, null: false, foreign_key: true

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

TYPES ARE HARDER AND POTENTIALLY IMPOSSIBLE TO GET PERFECT DEPENDING ON YOUR DATABASE

Slide 117

Slide 117 text

create_table :wrestlers do |t| t.string :name, null: false end add_index :wrestlers, :name, unique: true

Slide 118

Slide 118 text

ON TYPES

Slide 119

Slide 119 text

ON TYPES • These are the same wrestler: The Rock, THE ROCK, The Rock

Slide 120

Slide 120 text

ON TYPES • These are the same wrestler: The Rock, THE ROCK, The Rock • This is in no way the name of a wrestler: “ “

Slide 121

Slide 121 text

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?

Slide 122

Slide 122 text

create_table :wrestlers do |t| t.string :name, null: false end add_index :wrestlers, ”lower(name)”, unique: true

Slide 123

Slide 123 text

execute %{ ALTER TABLE wrestlers ADD CONSTRAINT no_blank_wrestler_names CHECK ( TRIM(name) <> '' )  }

Slide 124

Slide 124 text

class Wrestler < ApplicationRecord validates :name, presence: true, uniqueness: { case_sensitive: false } end

Slide 125

Slide 125 text

WHAT ABOUT NULL/nil?

Slide 126

Slide 126 text

No content

Slide 127

Slide 127 text

create_table :finishing_moves do |t| t.string :name, null: false t.references :wrestler, null: false, foreign_key: true end

Slide 128

Slide 128 text

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

Slide 129

Slide 129 text

Slide 130

Slide 130 text

Slide 131

Slide 131 text

No content

Slide 132

Slide 132 text

create_table :wrestlers do |t| t.string :name, null: false t.string :finishing_move, null: true,

Slide 133

Slide 133 text

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

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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.

Slide 136

Slide 136 text

NEVER FORGET THAT YOUR DATA IS MORE IMPORTANT THAN YOUR APP - TREAT IT THAT WAY!

Slide 137

Slide 137 text

RailsConf2019_rails6 DAVE COPELAND @DAVETRON5000 DAVETRON5000.COM