$30 off During Our Annual Pro Sale. View Details »

Database Design for Beginners

David Copeland
May 01, 2019
Programming
2
420

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.

David Copeland

May 01, 2019
Tweet

More Decks by David Copeland

See All by David Copeland
The fewer the concepts, the better the code?
davetron5000
3
620
Programming a Time Machine
davetron5000
0
510
Growing and Managing Distributed Teams
davetron5000
0
200
The Effective Remote Developer
davetron5000
0
280
Developer of Experience after 3.5 years of SAAS and PAAS
davetron5000
1
350
Can Time-Travel Keep You From Blowing Up The Enterprise?
davetron5000
0
370
Rails, Postgres, Angular, and Bootstrap: The Power Stack
davetron5000
0
670
Threads & JRuby: the simple alternative to evented
davetron5000
2
4.3k
Don't Fear the Threads: Simplify Your Life with JRuby
davetron5000
17
6.1k

Other Decks in Programming

See All in Programming
「価値」あるものを作るためにエンジニアができること - NIFTY Tech Day 2023
niftycorp
0
130
エンジニアもUIを作るならユーザーについて知ろう！
ohakutsu
1
120
ブランチ運用とデプロイフローを見直してリリースを楽にする
syukai
3
410
集団意思決定の落とし穴と誰も望まない技術的負債
h0r15h0
0
2.6k
機械学習ソフトウェアにおけるテスト手法
hitsuji1991
PRO
2
1k
Voicyの生放送リスナー画面で パフォーマンスチューニングした話
miyuki2203
0
110
コードを読みやすくしよう！〜秒で身に付くよい命名〜 (LT) - NIFTY Tech Day 2023
niftycorp
1
130
KDDI共同講演：@niftyメール基盤移行プロジェクト - NIFTY Tech Day 2023
niftycorp
0
120
飛び出せ！フロントエンド知見共有会~Next,Astro,Sveltekitを使ったエンジニアたちの声~ - NIFTY Tech Day 2023
niftycorp
0
130
個人開発がおすすめな理由
texmeijin
2
620
DIGGLEの分析基盤アーキテクチャ
zakky21
0
220
未定義動作でFizz Buzz / Undefined Fizz Buzz
kaityo256
PRO
1
120

Featured

See All Featured
Building Adaptive Systems
keathley
29
1.6k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
26
5.7k
A better future with KSS
kneath
230
16k
Web development in the modern age
philhawksworth
201
9.9k
10 Git Anti Patterns You Should be Aware of
lemiorhan
645
57k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
44
12k
Building Applications with DynamoDB
mza
87
5.3k
The Cult of Friendly URLs
andyhume
71
5.4k
Statistics for Hackers
jakevdp
788
220k
A designer walks into a library…
pauljervisheath
199
22k
Visualization
eitanlees
131
13k
The Pragmatic Product Professional
lauravandoore
23
5.4k

Transcript

  1. DATABASE DESIGN
    FOR BEGINNERS
    DAVE COPELAND
    @DAVETRON5000

    View Slide

  2. THOUGHT EXPERIMENT

    View Slide

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

    View Slide

  4. YOUR
    DATABASE
    GOES AWAY
    FOREVER
    WITH NO
    BACKUPS

    View Slide

  5. DATA > SOURCE CODE

    View Slide

  6. DATABASE DESIGN
    FOR BEGINNERS
    DAVE COPELAND
    @DAVETRON5000

    View Slide

  7. RailsConf2019_rails6
    DISCOUNT CODE:

    View Slide

  8. WHAT IS A
    “DATABASE”?

    View Slide

  9. OLTP
    ONLINE
    TRANSACTION
    PROCESSING

    View Slide

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

    View Slide

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

    View Slide

  12. WHAT FACTS
    DO WE
    WANT TO
    RECORD?
    STEP 1

    View Slide

  13. View Slide

  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.

    View Slide

  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.

    View Slide

  16. View Slide

  17. WRESTLERS
    NAME
    FINISHING MOVE
    SHOWS
    NAME
    TITLE

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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.

    View Slide

  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

    View Slide

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

    View Slide

  26. View Slide

  27. WHATEVER
    STORE ANYTHING

    View Slide

  28. WHATEVER
    RELATION
    (FIRST NORMAL FORM)
    STORE ANYTHING
    KNOWN ANOMALIES

    View Slide

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

    View Slide

  30. WHAT IS A RELATION?

    View Slide

  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

    View Slide

  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.

    View Slide

  33. CREATE
    RELATIONS
    STEP 2
    STEP 1

    WHAT FACTS DO WE WANT TO
    RECORD?

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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!

    View Slide

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

    View Slide

  47. FUNCTIONAL DEPENDENCIES

    View Slide

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

    View Slide

  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

    View Slide

  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.

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  55. KEYS

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  60. KEYS BASED ON BUSINESS RULES

    View Slide

  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

    View Slide

  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.

    View Slide

  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

    View Slide

  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)

    View Slide

  65. IDENTIFY
    FUNCTIONAL
    DEPENDENCIES
    AND KEYS
    STEP 3
    STEP 1

    WHAT FACTS DO WE WANT TO
    RECORD?
    STEP 2

    CREATE RELATIONS

    View Slide

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

    View Slide

  67. IMPLICATIONS OF KEYS AND FUNCTIONAL DEPENDENCIES

    View Slide

  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.

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  76. ADD THE DEPENDENT COLUMNS TO THAT TABLE
    WRESTLER FINISHING MOVE

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  86. HOW DID WE DO THIS?

    View Slide

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

    View Slide

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


    View Slide

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



    View Slide

  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




    View Slide

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

    View Slide

  92. PRIMARY KEYS

    View Slide

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

    View Slide

  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.

    View Slide

  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.

    View Slide

  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.

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  102. WRESTLER SHOWS
    WRESTLER ID
    SHOW ID
    WRESTLER
    SHOW
    MATCH

    View Slide

  103. WRESTLER SHOWS
    WRESTLER ID
    SHOW ID
    WRESTLER
    SHOW
    MATCH
    SHOWS
    SHOW ID
    WRESTLERS
    WRESTLER ID

    View Slide

  104. WRESTLER SHOWS
    WRESTLER ID
    SHOW ID
    WRESTLER
    SHOW
    MATCH
    SHOWS
    SHOW ID
    SHOW
    WRESTLERS
    WRESTLER ID
    WRESTLER

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  109. LOGIC TO PHYSICAL

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  118. ON TYPES

    View Slide

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

    View Slide

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

    View Slide

  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?

    View Slide

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

    View Slide

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

    }

    View Slide

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

    View Slide

  125. WHAT ABOUT
    NULL/nil?

    View Slide

  126. View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  131. View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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.

    View Slide

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

    View Slide

  137. RailsConf2019_rails6
    DAVE COPELAND
    @DAVETRON5000
    DAVETRON5000.COM

    View Slide