Bringing Good Design to the Table

Database design talk covering normalization, best practices, anti-patterns, and naming conventions. Given at SunshinePHP.

Transcript

1. Bringing good design to the table Photos ©James McAdam 2002
2. Who am I? !   Brian Fenton !   Senior Software Engineer at Mashery !   We’re hiring! !   Primarily a PHP developer !   Really enjoy OO and database design
3. Overview
4. So you’ve been asked to build a system…
5. Shadow your users
6. Primary Keys
7. Natural keys
8. Foreign keys
9. Relationships
10. Constraints
11. Define columns strictly
12. Cleaning data
13. Database Normalization Edgar F. Codd Database Pimp
14. But de-normalized is faster!
15. No duplicate data
16. Fields should only store a single data value
17. Don’t store what you can calculate
18. Dates
19. Pop Quiz, Hotshot ID Value 1 1 2 NULL 3 5 4 6 5 NULL 6 4 7 8 8 NULL 9 2 10 2 Total: 28 SELECT AVG(Value) = ?
20. NULL means Unknown
21. Naming Conventions
22. Tables employee is Bad employees is OK personnel is Better
23. Linking Tables customer_addresses teacher_courses employee_salaries
24. Columns id address_id postal_code locality region Yes No cust_no addr zipCode customer_name Reserved words
25. Normalization exercise ISBN title author price 0-943396-04-2 Design Patterns: Elements of Reusable Object-Oriented Software Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides 29.99 0-684-84328-5 Joe Celko's Trees and Hierarchies Joe Celko 32.95 0-85131-041-9 Refactoring: Improving the Design of Existing Code Martin Fowler 19.99 9971-5-0210-0 Code Complete 2nd Edition Steve McConnell 25.50
26. Normalization exercise ISBN title price 0-943396-04-2 Design Patterns 29.99 0-684-84328-5 Trees and Hierarchies 32.95 0-85131-041-9 Refactoring 19.99 9971-5-0210-0 Code Complete 2 25.50 id author 1 Erich Gamma 2 Richard Helm 3 Ralph Johnson 4 John Vlissides 5 Joe Celko 6 Martin Fowler 7 Steve McConnell books authors
27. ISBN title price 0-943396-04-2 Design Patterns 29.99 0-684-84328-5 Trees and Hierarchies 32.95 0-85131-041-9 Refactoring 19.99 9971-5-0210-0 Code Complete 2 25.50 id author 1 Erich Gamma 2 Richard Helm 3 Ralph Johnson 4 John Vlissides 5 Joe Celko 6 Martin Fowler 7 Steve McConnell ISBN author_id 0-943396-04-2 1 0-943396-04-2 2 0-943396-04-2 3 0-943396-04-2 4 0-684-84328-5 5 0-85131-041-9 6 9971-5-0210-0 7 books authors books_authors
28. New Student Orientation Name Address City State Zip Term Date Brian Fenton 717 Market St San Francisco CA 94112 Summer 1999 5-9-99 Chris Smith 1600 Pennsylvania Avenue Washington DC 20500 Fall 1999 7-10-99 John Andrews 221B Baker St London MO 66614 Spring 2000 11-10-99 Susan Anthony 123 Elm St Anytown MA 00128 Fall 1999 7-8-99
29. Storing names
30. UTF-8
31. Indexing
32. Anti-patterns
33. Multiples of the same column id teacher course1 course2 course3 1 Steve Fluty MATH101 BIO 210 CHEM 531 2 Bob Levine PSYC 400 CLSX 356 PHYS 108 3 Kim Jones FREN 110 NULL NULL
34. id name 1 Steve Fluty 2 Bob Levine 3 Kim Jones id department level name description 1 MATH 101 College Mathematics … 2 PSYC 400 Neural Psychology … 3 FREN 110 Intermediate French … 4 BIOL 210 Microbiology … 5 CHEM 531 Organic Chemistry … teacher_id course_id 1 1 2 2 3 3 1 4 1 5 teachers teacher_courses courses
35. Type codes determining columns used id name type cost flavor gallons number_of_blades 1 Flushmaster toilet 199.99 NULL 1.3 NULL 2 Slashmaster tool 35.99 NULL NULL 5 5 Baby-Gro food 1.29 carrots NULL NULL 6 Flushmaster Deluxe toilet 249.99 NULL 1.5 NULL 10 Health Muck food 1.29 peas NULL NULL
36. Solution 1 id name cost gallons 1 Flushmaster 199.99 1.3 6 Flushmaster Deluxe 249.99 1.5 id name cost flavor 5 Baby-Gro 1.29 carrots 10 Health Muck 1.29 peas id name cost number_of_blades 2 Slashmaster 35.99 5 toilets baby_foods multitools
37. Solution 2 id name cost 1 Flushmaster 199.99 2 Slashmaster 35.99 5 Baby-Gro 1.29 6 Flushmaster Deluxe 249.99 10 Health Muck 1.29 product_id number_of_blades 2 5 product_id gallons 1 1.5 6 1.3 product_id flavor 5 carrots 10 peas products baby_foods toilets multitools
38. Hierarchical/tree relationships author content bfenton Woohoo, I’m at SunshinePHP! author content elazar Me too! author content adamculp Welcome, Brian! author content jmather Hey, didn’t know you were here author content elazar Yep, just got in
39. Solution 1 id parent_id author content 1 NULL bfenton Woohoo, I’m at SunshinePHP! 2 1 elazar Me too! 3 1 adamculp Welcome, Brian! 4 2 jmather Hey, didn’t know you were here 5 4 elazar Yep, just got in
40. Solution 2 author content bfenton Woohoo, I’m at SunshinePHP! author content elazar Me too! author content adamculp Welcome, Brian! author content jmather Hey, didn’t know you were here author content elazar Yep, just got in 1 2 3 4 5 6 7 8 9 10
41. Solution 2 id author left right content 1 bfenton 1 10 Woohoo, I’m at SunshinePHP! 2 elazar 4 9 Me too! 3 adamculp 2 3 Welcome, Brian! 4 jmather 5 8 Hey, didn’t know you were here 5 elazar 6 7 Yep, just got in
42. Solution 3 id path author content 1 1/ bfenton Woohoo, I’m at SunshinePHP! 2 1/2/ elazar Me too! 3 1/3/ adamculp Welcome, Brian! 4 1/2/4/ jmather Hey, didn’t know you were here 5 1/2/4/5/ elazar Yep, just got in
43. Solution 4 id author content 1 bfenton Woohoo, I’m at SunshinePHP! 2 elazar Me too! 3 adamculp Welcome, Brian! 4 jmather Hey, didn’t know you were here 5 elazar Yep, just got in parent child 1 1 1 2 1 3 1 4 1 5 2 2 2 4 2 5 3 3 4 4 4 5 5 5
44. Wrap up !   Gather requirements !   Choose good keys !   Define relationships !   Be strict !   No duplicate/calculated data !   No multi-valued columns !   Dates as UTC !   Text as UTF-8 !   Use a naming convention !   Be smart about NULLs !   Avoid anti-patterns !   More tables is usually good !   Index where you need !   “It Depends”
45. Resources ! Database Design for Mere Mortals by Michael J. Hernandez ! How to support full Unicode in MySQL databases ! Personal names around the world ! Falsehoods Programmers Believe About Names ! Falsehoods programmers believe about time ! How to Design Indexes, Really
46. Questions? @brianfenton www.brianfenton.us bfenton@mashery.com https://joind.in/8026