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. About Me • Father of 3 • Volunteer at Community

    Justice, Inc. • Lead Developer at National Conference of Bar Examiners • Home Pizza Chef
  2. 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
  3. Relational databases • Data integrity • Reporting/summarizing • Auditing •

    Transactions • Solid design • ACID (Atomicity, Consistency, Isolation, Durability)
  4. Natural Key v. Surrogate Key •A surrogate key has no

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

    gets administrative rights. Problems?
  7. Optimizing Queries •RDMS creates query plan •Query Optimizer finds least

    expensive path • Table design/indexes are used
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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/
  13. 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?
  14. Thanks • Derek Binkley • @DerekB_WI • http://derekb-wi.com • Feedback

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