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.

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