Lone Star PHP - 2016: Database Theory, Models and Abstractions

Proper database theory and design seems to be an increasingly lost art. As abstraction layers, migration tools and ORM become more prevalent, less focus is being spent on properly architecting the database, relationships, indexes and constraints. As a result, many projects suffer a crisis of data or scalability as data sets grow.

In this talk we'll cover: Table relationships and relationship tables Database Normalization with a focus on Third Normal Form (3NF) Database Indexes and Use Cases Database Keys and Composite Keys Database Optimization. The difference between schema and schema-less databases, and finding the proper database to suit your requirements.

Transcript

1. EUGENE T. COOK LONE STAR PHP 2016 #LSP16 DATABASE THEORY, MODELS AND ABSTRACTIONS
2. A BIT ABOUT ME Transactional and Start Up Attorney MBA - IT and 
 Operations Management Polymathic Technologist JD - Rule of Law in
 Developing Nations Shameless Plugs
3. TWO PRIMARY TRENDS IN DB DESIGN SQL/SCHEMA NOSQL (SCHEMALESS)* MySQL MongoDB MariaDB RethinkDB PostgreSQL Redis Google Big Table * THERE ARE EXCEPTIONS TO MANY OF THE CHARACTERISTICS OF TYPICAL SCHEMALESS STORES
4. QUICK PRIMER DATA MODELING
5. DATA MODELING - STRUCTURE PROJECT SCOPE YOU’VE BEEN ASKED BY HEAD MISTRESS GERTRUDE TO CREATE A SYSTEM FOR HER NEW BOARDING SCHOOL. SHE WANTS TO BE ABLE TO TRACK CLASSES, THEIR STUDENTS AND ALSO KNOW THEIR LOCATION WITHIN THE SCHOOL!
6. MONOLITHIC TABLE COURSEID COURSE HALL MONITOR STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH SAM ERICA 102 SCIENCE WEST JOSEPH JOE JAKE ERICA 103 GEO SOUTH WILMA JANE MAX 104 COMP SOUTH WILMA CHUCK
7. THAT WAS EASY! ANY QUESTIONS?
8. GERTRUDE DOES NOT APPROVE DUPLICATION OF DATA MODIFICATION ANOMALIES NORMALIZE!!!!
9. I THOUGHT I WAS DONE
10. QUICK PRIMER DB MODELING
11. DATABASE MODEL DATABASE MODEL "A DATABASE MODEL IS A TYPE OF DATA MODEL THAT DETERMINES THE LOGICAL STRUCTURE OF A DATABASE AND FUNDAMENTALLY DETERMINES IN WHICH MANNER DATA CAN BE STORED, ORGANIZED, AND MANIPULATED.” WIKIPEDIA
12. CONCEPT • Identifying the distinct entities that comprise your data’s architecture • Conceptualizing your data objects • Identifying the relationships between the data objects DATABASE MODELING DESIGN • Conversion of the data objects into an actual schema • Naming convention of the data and relationships • Deciding what constraints you should have
13. MONOLITHIC TABLE COURSEID COURSE HALL MONITOR STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH SAM ERICA 102 SCIENCE WEST JOSEPH JOE JAKE ERICA 103 GEO SOUTH WILMA JANE MAX 104 COMP SOUTH WILMA CHUCK NORMALIZATION LOGICALLY ORGANIZING MODEL INTO TABLES AND COLUMNS CARDINALITY THE FUNDAMENTAL RELATION OF ONE TABLE TO ANOTHER
14. WHY NORMALIZE? MINIMIZE DUPLICATION MODIFICATION ANOMALIES (FIDELITY) SIMPLIFY QUERIES COURSEID COURSE HALL STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH ERICA 102 SCIENCE WEST JOE JAKE ERICA 103 GEO SOUTH JANE MAX 104 COMP SOUTH CHUCK
15. MODIFICATION ANOM. INSERT ANOMALY COURSEID COURSE HALL STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH ERICA 102 SCIENCE WEST JOE JAKE ERICA 103 GEO SOUTH JANE MAX 104 COMP SOUTH CHUCK ? EAST ?
16. UPDATE ANOMALY UPDATE ANOMALY COURSEID COURSE HALL STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH ERICA 102 SCIENCE WEST JOE JAKE ERICA 103 GEO SOUTH JANE MAX 104 COMP SOUTH CHUCK
17. FIDELITY ANOMALY FIDELITY ANOMALY COURSEID COURSE HALL STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH ERIKA 102 SCIENCE WEST JOE JAKE ERICA 103 GEO SOUTH JANE MAX 104 COMP SOUTH CHUCK
18. DELETE ANOMALY DELETE ANOMALY COURSEID COURSE HALL STUDENT1 STUDENT2 STUDENT3 101 LAW NORTH ERICA 102 SCIENCE WEST JOE JAKE ERICA 103 GEO SOUTH JANE MAX 104 COMP SOUTH CHUCK
19. NORMALIZATION COURSEID COURSE HALL 101 LAW NORTH 102 SCIENCE WEST 103 GEO SOUTH 104 COMP SOUTH FIRST NORMAL FORM - 1NF STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK PRIMARY KEY ONLY ATOMIC VALUES NO REPEATED COLUMNS
20. NORMALIZATION COURSEID COURSE HALL 101 LAW NORTH 102 SCIENCE WEST 103 GEO SOUTH 104 COMP SOUTH 1NF - WHAT DID WE GAIN? STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK UNLIMITED STUDENTS SIMPLER QUERY OF STUDENT FIXED STUDENT ANOMALIES
21. NORMALIZATION COURSEID COURSE 101 LAW 102 SCIENCE 103 GEO 104 COMP SECOND NORMAL FORM - 2NF STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK TABLE MUST BE 1NF ALL NON-KEY COLUMNS (ATTRIBUTES) ARE PK DEPENDENT HALLID HALL N NORTH W WEST S SOUTH E EAST
22. NORMALIZATION 2NF - WHAT DID WE GAIN? ANOMALIES HAVE BEEN RESOLVED COURSEID COURSE 101 LAW 102 SCIENCE 103 GEO 104 COMP STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK HALLID HALL N NORTH W WEST S SOUTH E EAST
23. NORMALIZATION WE NEED MORE DATA STUDENTID STUDENT CITY ZIP S1 ERICA ADDISON 75001 S2 JAKE JACKSON 25343 S3 MAX HARTLETT 53335 S4 JOE ADDISON 75001 S5 JANE WAYNE 12345 S6 CHUCK DALLAS 75243
24. GERTRUDE DOES NOT APPROVE I SENSE AN ANOMALY!!!! NORMALIZE!!!!
25. NORMALIZATION THIRD NORMAL FORM - 3NF STUDENTID STUDENT ZIP S1 ERICA 75001 S2 JAKE 25343 S3 MAX 53335 S4 JOE 75001 S5 JANE 12345 S6 CHUCK 75243 TABLE IS IN 2NF NO TRANSITIVELY DEPENDENT ATTRIBUTES ZIP CITY 75001 ADDISON 25343 JACKSON 53335 HARTLETT 12345 WAYNE 75243 DALLAS
26. BUT THE OBJECTS ARE LONELY!
27. ONE TO ONE CARDINALITIES (RELATIONSHIPS) BELONGS TO (FK) / HAS ONE ONE TO MANY BELONGS TO (FK) / HAS MANY MANY TO MANY HAS MANY THROUGH / HAS MANY THROUGH
28. RELATIONSHIPS ONE TO MANY RELATIONSHIPS COURSEID COURSE HALLID 101 LAW N 102 SCIENCE W 103 GEO S 104 COMP S HALLID HALL N NORTH W WEST S SOUTH E EAST
29. RELATIONSHIPS MANY TO MANY RELATIONSHIPS COURSEID COURSE 101 LAW 102 SCIENCE 103 GEO 104 COMP STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK
30. RELATIONSHIPS RELATIONAL / INTERSECTION TABLE COURSEID COURSE 101 LAW 102 SCIENCE 103 GEO 104 COMP STUDENTID STUDENT S1 ERICA S2 JAKE S3 MAX S4 JOE S5 JANE S6 CHUCK COURSEID STUDENTID 101 S1 102 S1 103 S3 104 S3
31. VISUALIZING THE RELATIONSHIPS
32. QUICK PRIMER OPTIMIZATION
33. INDEXES DATA OPTIMIZATIONS HIGHLY EFFICIENT COPY OF SELECT COLUMNS WHICH TYPICALLY INCLUDE DIRECT ADDRESSING STORED PROCEDURES ROUTINES RUN WITHIN THE DATABASE THAT CAN SOMETIMES MORE EFFICIENTLY MANIPULATE DATA OR REDUCE DB TRAFFIC VIEWS STORED QUERIES OF DATA WHICH CAN BE INDEXED SEPARATELY AND IN SOME DATABASES, THE RESULTING DATASET IS INDEXED AS A CLUSTER, DRAMATICALLY INCREASING PERFORMANCE
34. INDEXES CREATE AN INDEX FOR ATTRIBUTES THAT YOU QUERY AGAINST THE MOST. INDEXES ARE UPDATED DYNAMICALLY, AND WILL RESULT IN DRAMATICALLY IMPROVED QUERIES.
35. STORED PROCEDURES CREATE STORED PROCEDURES FOR STATICALLY CALLED SQL THAT MANIPULATES OR QUERIES LARGE DATASETS FOR SUBSETS VIEWS USE VIEWS FOR COMMON JOINS, WHICH MANY RDBMS’ KEEP IN THE A SEPARATE TABLE CACHE. THIS WILL ALLOW DIRECT QUERIES TO THE JOINED TABLE.
36. PROCESS INITIAL ‘COMMIT’ ANTICIPATE WHAT THE COMMON QUERIES WILL BE ITERATE LOG SLOW QUERIES, RESOLVE AND KEEP OPTIMIZING
37. QUICK PRIMER NOSQL
38. SCHEMA-LESS? • NoSQL databases don’t have an internal schema • NoSQL databases are dynamically typed, meaning attributes can even have different types • Uses documents instead of records, which can be thought of as objects or arrays • Documents have no inherent schema • Typically no explicitly defined relationships • *** Schemas, constraints and types are all “soft.” They rely on programming or abstractions rather than “hard” in the DB. *** WHAT’S NOSQL?
39. RELATIONSHIPS DOCUMENT SCHEMAS AREN’T DEFINED COURSEID COURSE COURSE LAW HALL NORTH STUDENTS JAKE, JANE, MAX DENORMALIZATION MAY INCLUDE DEP. OR TRANSITIVE INFORMATION (WRITE TO INCREASE EFFICIENCY OF READS)
40. • Flexible documents • Fast iteration • More agile (yes the pm) • No schema “migrations” required • “Web Read” (JSON/BSON) • “Web Scale” (Performance) • “Black Box” (E.g. GIS) • Data fidelity • Soft constraints are programmatic • Most NoSQL not built for joins • Requires optimization of writes for reads • Refactoring, when necessary, is computationally intensive
41. WEB SCALE11!!1!1!ELEVENTYONE!
42. BRO, DO YOU EVEN WEBSCALE? • “Web Scale” - delivering scalable, consistent performance, especially through distributed reads. • Achieved through denormalization. • Relational data is kept in the document itself rather than separated out into separate tables.
43. DENORMALIZATION? YOU JUST WASTED THE FIRST 20 MINS.
44. RDBS • Sophisticated joins • Fidelity of data is paramount • Complex relationships • Static data model • Multi-row transactions • Less sophisticated DBA • Can boost performance with followers (slaves) WHEN TO USE NOSQL • Complex data (E.g. geo-spatial) • Changing data model • Horizontal inconsistency not necessarily an issue • Some great features specifically for webapps (E.g. oplog in MongoDB) • Distributed read performance is critical • More sophisticated DBA willing to maintain, cleanse data and normalize types.
45. !!DANGER!!DANGER!!DANGER!!
46. BE CAREFUL WHAT YOU WISH FOR • More flexible does not mean simpler! • Structure and sanitization is app’s responsibility • Requires more effort from dev to ensure data fidelity NOSQL IS *NOT* SIMPLER OR EASIER
47. ;)/*+4'#-)'7)654505+"+)*()<'B45&'( ) ,RS+T!8EUVW!X!M#!6'D#!2E!UVW! ! ,RY+T!8EUVW!X!Z2E3!UVW!! *+++T!8EUVW!X!8E!UVW[!! *++\T!8EUVW!X!8E9!E2G(!UVW!! *+,-T!8EUVW!X!8E)!UVW[! N]O^=N.UO ! MARK MADSEN
48. NOSQL IS NOT EASIER • No right or wrong answer. • Not mutually exclusive - can be used together with great effect. • E.g. Comments system might be on NoSQL but your HR system might be on RDBS. • Key / Value stores like Redis = great object or query cache
49. RECOMMENDATION POSTGRES?
50. QUICK PRIMER ABSTRACTIONS
51. ABSTRACTIONS • API or library which obfuscates some of the raw querying or manipulation of the database. • Can allow for easy drop in of other database types (E.g. MySQL to Postgres) • Some add validation or schema support to databases that might not have them by default. • Some abstractions also offer data migrations, handling the table structure for you and can even build your original data model from an existing schema. ABSTRACTIONS EXAMPLES • Propel • Doctrine • PDO • Mongoose • Monk
52. LOOK FORWARD DON’T BE AFRAID TO BROADEN YOUR HORIZONS YOUR APP WILL THANK YOU
53. KEEP IN TOUCH @ETC ECOOK E@LAW.FM
54. QUESTIONS?
55. THANK YOU