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