Five Things All Developers Should Know About Databases

D12eaf3ef46e4f0fc6b714fd2b7ffe3b?s=47 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.

D12eaf3ef46e4f0fc6b714fd2b7ffe3b?s=128

derek-b

November 11, 2017
Tweet

Transcript

  1. Five Things All Developers Should Know About Databases Chippewa Valley

    Code Camp @DerekB_WI www.derekb-wi.com
  2. About Me • Father of 3 • Volunteer at Community

    Justice, Inc. • Lead Developer at National Conference of Bar Examiners • Home Pizza Chef
  3. Introduction 1. Use cases 2. Keys 3. Optimization 4. Power

    of RDBMS 5. Design
  4. Use Cases for Relational Databases

  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
  6. Relational databases • Data integrity • Reporting/summarizing • Auditing •

    Transactions • Solid design • ACID (Atomicity, Consistency, Isolation, Durability)
  7. Non-relational data •Streams/non-transactional •Flexible data structure •High scalability

  8. Keying your data

  9. Primary Key •Unique •Not null •Unchanging

  10. Natural Key v. Surrogate Key •A surrogate key has no

    meaning •A natural key is a unique data attribute(s)
  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
  12. Keys with meaning • ID with a value < 100

    gets administrative rights. Problems?
  13. Optimization

  14. Optimizing Queries •RDMS creates query plan •Query Optimizer finds least

    expensive path • Table design/indexes are used
  15. Explain Plan Example Sample users table Data generated with https://github.com/fzaninotto/Faker

  16. Explain Plan Example Sample queries Create statement

  17. Explain Plan Example Explain the query plan Results

  18. Explain Plan Example Explain the query plan Results

  19. Power of RDMS

  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
  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
  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
  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
  24. Design

  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/
  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?
  27. Structure It • This is easily readable

  28. Thanks • Derek Binkley • @DerekB_WI • http://derekb-wi.com • Feedback

    at http://chippewavalleycodecamp.com/2017/feedback.html