Why the sequel to NoSQL is SQL: an intro to table-groups

We are told to think that SQL is the source of all database evil. What if it's not? What if the problem is deeper than that? This presentation is about table-groups, a fundamental solution to the relational model.

Transcript

2. renormalize  
3.     Four  days  in  the  life  of  a  developer  
4. Day  1     Wake  up  with  brilliant  idea.   I’m  going  to  start  my  next  business  and  it’s  going   to  be  big!  
5. Day  2     Idea  sBll  brilliant.  Good  sign!   Setup  my  app  (PHP?  Ruby?),  a  few  main   applicaBon  objects  like  a  user  with  his  list  of   orders,  items  and  every  login  he  ever  made.  
6. Day  3     Ok  ,got  my  app..  next  to  mysql..  Use  an  ORM..   Crap,  every  object  just  exploded  into  1000  liUle   pieces;  1000  tables.  What  was  that  curse  word?   NORMALIZATION?  Why  oh  Codd,  Why???  
8. Day  4     Hmm..  Been  there  before.  I  know  if  I  don’t   handle  this  now  I’ll  live  to  regret  this  when   traffic  crashes  my  database.     Think,  think,  think,  
9.     Less  tables?     Yeah  right  when  do  you  ever  store  less…  
10.     Denormalize  to  materialized  views?  Good  for   queries  but  writes  would  suck…  
11.     Serialize  the  objects  into  a  blob  in  mysql!  But   what  about  queries?  Do  everything  in  the  app?  
12.     memcache  to  cache  object?  Doesn’t  work  for   dynamic  data.  
13.     Drop  SQL?  Mongo?  Couchbase?  Tokyo  Cabinet?    
14.     Driving  me  nuts.     I  know  the  value  of  SQL,  but  seriously…  
15. Hold  on!  Rewind  please.       Let’s  take  another  look  at  that  schema…  
17.     My  objects  are  there,  just  exploded  into  so  many   tables.  If  only  there  was  a  way  to  expose  that…  
18. Something  like:      
19.     Personal  conclusion:  I  need  a  beUer  SQL  database  
20. Core  concept:  table-­‐grouping       Table groups
21.     This  is  nice  visually,  but  becomes  really  powerful   in  the  physical  model  –  table  groups  REPLACE   tables  as  the  first  class  physical  things  in  the   database.    
22. t1.c1   Table   data   t1.c2   t1.c3   t2.c1   t2.c2   t2.c3   t3.c1   t3.c2   t3.c3   Logical  table  1:  user   Logical  table  2:  order   Logical  table  3:  item   1   John   Way   2   Ari   Weil   10   1/4   ship   11   2/7   proc   12   6/10   pick   100   2323   book   101   2323   game   102   1212   bag   Table   data   Table   data   Rather  than  storing  tables  –   store  groups!  
23. As  if  shuffling  rows…      
24. t1.c1   t1.c2   t1.c3   t2.c1   t2.c2   t2.c3   t3.c1   t3.c2   t3.c3   Logical  table  1:  user   Logical  table  2:  order   Logical  table  3:  item   1   John   Way   2   Ari   Weil   10   1/4   ship   11   2/7   proc   12   6/10   pick   102   1212   bag   101   2323   book   100   3434   game   physically  data  is   interleaved   Table   Group   Rows  from  tables  within  a  group  are   interleaved   Logically  same  schema   and  SQL  
25.     It’s  nice  
26. Because   Document  like  access     SQL  that’s  fast     Natural  data  distribuBon  across  nodes  
27.     Documents  from  a  relaBonal  database?  
28.     Nested  SQL  
29. SELECT user_id, name, (SELECT order_id, date, (SELECT item_id, desc FROM items WHERE items.order_id = orders.order_id), FROM orders WHERE orders.user_id = users.user_id), FROM users WHERE user_id = 1;
30.     Nested  resultset  
31. { "users" : { "user_id": 1, "first": "John", "last": "Way", "Orders" : [ { "order_id": 10, "date": "1/4/2011", "Items" : [ { "item_id": 102, "desc": "bag" }, { "item_id": 109, "desc": "shoes" } ] }, { "order_id": 11, "date": "2/7/2011", "Items" : [ { "item_id": 117, "desc": "laptop" }, { "item_id": 123, "desc": "lamp" } ] } ] } }
33.     SQL  ExecuBon  plans  think,  breathe  and  operate   on  groups.  Joins  inside  of  a  group  are  free.     It  changes  everything.  
34. SELECT u.id , o.date, .... FROM users u JOIN orders o ON u.id = o.u_id JOIN items i ON o.id = i.o_id WHERE u.status = 'stuck' AND i.type = 'Book' ORDER BY o.latestChange DESC LIMIT 50
35. RelaBonal   1   2   3   4   7   6   5   8   9   3 Index Accesses Sort Temp Table 2 Joins 2 Table Accesses Project Results 10  
36. Akiban   2   1   3   1 Group Index Access 1 Group Access Project Results
38. Akiban  Server   Use  stand  alone…  
39. Akiban  Server   MyISAM  /  InnoDB   Storage   MySQL  Master   MySQL adapter ReplicaBon   Or  alongside  mysql  
40.     ori@akiban.com      
41.     ori@akiban.com     irc.freenode.net,  #akiban   launchpad.net/akiban