Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Five Things All Developers Should Know About Databases

derek-b
November 11, 2017

Five Things All Developers Should Know About Databases

Throughout my career I have encountered many developers that don’t understand data storage and manipulation. This often leads to designs that create significant technical debt. This talk explores way to avoid these problems.

Every project handles some type of data and there have never been more ways to store that data than there are now. When is a relational database the best choice? How do you take advantage of the power inside your relational database engine? How do you design your data to let your app grow over time? We will explore these topics and more so that you can take your development skills to the next level by learning to love data.

derek-b

November 11, 2017
Tweet

More Decks by derek-b

Other Decks in Technology

Transcript

  1. Five Things All Developers
    Should Know About Databases
    Chippewa Valley Code Camp
    @DerekB_WI
    www.derekb-wi.com

    View Slide

  2. About Me
    • Father of 3
    • Volunteer at Community Justice, Inc.
    • Lead Developer at National Conference
    of Bar Examiners
    • Home Pizza Chef

    View Slide

  3. Introduction
    1. Use cases
    2. Keys
    3. Optimization
    4. Power of RDBMS
    5. Design

    View Slide

  4. Use Cases for Relational
    Databases

    View Slide

  5. Common ways to store data
    • Relational database (MySQL, SQL Server, Oracle, Postgres)
    • NoSQL/document database (CouchDB, MongoDB)
    • Key-value store (Redis)
    • File system
    • Custom Built Solutions

    View Slide

  6. Relational databases
    • Data integrity
    • Reporting/summarizing
    • Auditing
    • Transactions
    • Solid design
    • ACID (Atomicity, Consistency, Isolation, Durability)

    View Slide

  7. Non-relational data
    •Streams/non-transactional
    •Flexible data structure
    •High scalability

    View Slide

  8. Keying your data

    View Slide

  9. Primary Key
    •Unique
    •Not null
    •Unchanging

    View Slide

  10. Natural Key v. Surrogate Key
    •A surrogate key has no meaning
    •A natural key is a unique data attribute(s)

    View Slide

  11. Natural Key May Change
    • A primary key must be stable over time.
    • In the United States, two digit state codes are historically stable.
    Examples of data thought to be stable may not be.
    • Country code – Yugoslavia? Soviet Union?
    • SSN – Encrypt?
    • Naturalized surrogate key

    View Slide

  12. Keys with meaning
    • ID with a value < 100 gets administrative rights.
    Problems?

    View Slide

  13. Optimization

    View Slide

  14. Optimizing Queries
    •RDMS creates query plan
    •Query Optimizer finds least expensive path
    • Table design/indexes are used

    View Slide

  15. Explain Plan Example
    Sample users table
    Data generated with https://github.com/fzaninotto/Faker

    View Slide

  16. Explain Plan Example
    Sample queries
    Create statement

    View Slide

  17. Explain Plan Example
    Explain the query plan
    Results

    View Slide

  18. Explain Plan Example
    Explain the query plan
    Results

    View Slide

  19. Power of RDMS

    View Slide

  20. Use the Strengths of the Database
    • DB Database is not just a storage engine.
    • Powerful platform for sorting, filtering, grouping and summarizing
    data.
    • Transactions are your friends

    View Slide

  21. Domain Logic In Memory
    • Logic is entirely in your PHP code, database is merely used for
    storage.
    • Easy to refactor Easy to unit test
    • Part of your version control system
    • Easy to deploy
    • Inefficient use of SQL
    • High overheard for database connections and queries

    View Slide

  22. Domain Logic In Database
    • Some logic is in SQL (procedures, views or direct SQL)
    • More efficient:
    • less memory in PHP
    • less connection time
    • SQL statements will be more efficient

    View Slide

  23. Object Relational Mapping - ORM
    • Simplifies data access by mapping tables to objects.
    • More work in code
    • Doesn’t have to map one to one to tables
    https://martinfowler.com/bliki/OrmHate.html

    View Slide

  24. Design

    View Slide

  25. Database Normalization
    1st Normal Form
    • Primary Key enforcing uniqueness
    • Consistent Field Types
    • Consistent Column Count
    2nd Normal Form
    • Parent/Child Relations
    • Foreign Keys
    3rd Normal Form
    • No transitive dependencies
    https://www.essentialsql.com/get-ready-to-learn-sql-11-database-third-normal-
    form-explained-in-simple-english/

    View Slide

  26. Structure It
    • No clearly defined columns
    • Flexibility for users
    • Very difficult for future developers
    • How would you map this to an object in your code?

    View Slide

  27. Structure It
    • This is easily readable

    View Slide

  28. Thanks
    • Derek Binkley
    • @DerekB_WI
    • http://derekb-wi.com
    • Feedback at
    http://chippewavalleycodecamp.com/2017/feedback.html

    View Slide