Five Things All Developers
Should Know About Databases
Chippewa Valley Code Camp
@DerekB_WI
www.derekb-wi.com
Slide 2
Slide 2 text
About Me
• Father of 3
• Volunteer at Community Justice, Inc.
• Lead Developer at National Conference
of Bar Examiners
• Home Pizza Chef
Slide 3
Slide 3 text
Introduction
1. Use cases
2. Keys
3. Optimization
4. Power of RDBMS
5. Design
Slide 4
Slide 4 text
Use Cases for Relational
Databases
Slide 5
Slide 5 text
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
Non-relational data
•Streams/non-transactional
•Flexible data structure
•High scalability
Slide 8
Slide 8 text
Keying your data
Slide 9
Slide 9 text
Primary Key
•Unique
•Not null
•Unchanging
Slide 10
Slide 10 text
Natural Key v. Surrogate Key
•A surrogate key has no meaning
•A natural key is a unique data attribute(s)
Slide 11
Slide 11 text
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
Slide 12
Slide 12 text
Keys with meaning
• ID with a value < 100 gets administrative rights.
Problems?
Slide 13
Slide 13 text
Optimization
Slide 14
Slide 14 text
Optimizing Queries
•RDMS creates query plan
•Query Optimizer finds least expensive path
• Table design/indexes are used
Slide 15
Slide 15 text
Explain Plan Example
Sample users table
Data generated with https://github.com/fzaninotto/Faker
Slide 16
Slide 16 text
Explain Plan Example
Sample queries
Create statement
Slide 17
Slide 17 text
Explain Plan Example
Explain the query plan
Results
Slide 18
Slide 18 text
Explain Plan Example
Explain the query plan
Results
Slide 19
Slide 19 text
Power of RDMS
Slide 20
Slide 20 text
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
Slide 21
Slide 21 text
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
Slide 22
Slide 22 text
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
Slide 23
Slide 23 text
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
Slide 24
Slide 24 text
Design
Slide 25
Slide 25 text
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/
Slide 26
Slide 26 text
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?