Transcript Brian`s PowerPoint Presentation

Accelerating PHP
development
Data-tier programming
Brian Rosenthal
Zvi Boshernitzan
Robocommerce
Overview: Data modeling


High-level approach to database design.
We will describe


Basic principles of data modeling
PHP Programming techniques



Database abstractions
Stack-based database programming
Inference-based database programming
Basic principles of data modeling


Normalization: “Don’t repeat yourself.”
Entity Relation “ER” diagrams
Normalization
“Don’t Repeat Yourself ”


First approach: store
the author information
within the book.
Problem: what if there
is more than one
author. Where do we
store the second
author?
Books
-title
-isbn
-author_first_name
-author_last_name
Normalization
“Don’t Repeat Yourself ”


Second approach:
store up to three
authors
Problems: what if
there are more, and
what about all of the
information about the
authors (address, etc.)
Books
-title
-isbn
-author1_first_name
-author1_last_name
-author2_first_name
-author2_last_name
-author3_first_name
-author3_last_name
Normalization
“Don’t Repeat Yourself ”




Third approach: have
two tables: books
and authors.
Authors have a book
id.
“Each book may be
written by 0 or more
authors.”
Each author must be
the writer of a book.
Books
-title
-isbn
1
0..*
-written by
-writer of
Authors
-book_id
-first_name
-last_name
Normalization
“Don’t Repeat Yourself ”

Problem: We still have redundancy
Books
id
title
Authors
id
book_id
first_name
last_name
phone_number
1 Da Vinci Code
2 Angels and Demons
1
1 Dan
Brown
800-808-0808
2
2 Dan
Brown
800-808-0808
Normalization
“Don’t Repeat Yourself ”



Fourth approach:
have three tables:
books, people,
authorship.
Each entry in the
“authorship” table is
a fact. That a
particular person
participated in an
authorship of a book
No information is
repeated!
Persons
Books
-first_name
-last_name
-phone_number
-title
-isbn
1
0..*
-work of
-of
1
0..*
Authorship
-book_id
-author_id
-participate in
-of
ER Diagrams



This is an ER diagram.
You read it: “Each product must be sold under a
brand”
“Each brand may be the origin of 0 or more
products”
brand
category
1
0..*
-category for
1
-categorized by
0..*
keyword
1
0..*
-indexed of
-on
keyword_products
product
1
0..*
-indexed by
-for
-origin of
-sold under
PHP Database Infrastructure



Zvi: database abstractions.
Brian: stack-based SQL programming
Brian: inference-based SQL programming
Database “stack” programming
SQL as a big string versus SQL as a “stack”
 SQL is, after all structured.
 {‘select’:’firstname,lastname’
, ‘from’:’customers c’
, ‘ijoin’:{‘s’:{‘table’:’sites’, ‘on’:’s.id = c.site_id’}}
, ‘where’:[“c.name like ‘%brian%’”, “c.id > 10”]
, ‘limit’:10
, ‘offset’:5
}
 $q->add_where(…)
 $q->add_select(…), $q->set_select(…)
 $q->add_lojoin(…)
 $q->load_dict({‘select’:’c.address_id’, ‘where’:…})
 $q->to_sql()
 $q->to_dict()

SPEC DRIVEN DATABASE
PROGRAMMING




db::objects($q->to_sql())
db::assocs($q->to_sql())
db::string($q->to_sql())
db::object($q->to_sql())
INFERENCE-BASED DATABASE
PROGRAMMING


A lot of information is stored in the database
schema
The idea here is to use it where it is possible
to do so
Inferences from ER diagrams
… if you were writing classes to manage these database entities, you might
include:





products::mget_by_category(…)
products::mget_by_brand(…)
products::map_keyword(…)
products::mget_by_keyword(…)
keywords::mget_by_product(…)
1
0..*
-category for
1
-categorized by
0..*
keyword
1
0..*
brand
category
-indexed of
-on
keyword_products
product
1
0..*
-indexed by
-for
-origin of
-sold under
Enter: PHP 5




Call handler override
“function __call($m, $args)”
Mysql reflection
Singleton / factory semantics
Autoloads
You should really get all of these
functions for free:
mg(‘products’)->get($product_id)
mg(‘products’)->create(‘name’, ‘lawnmower’)
mg(‘products’)->mget_by_keyword($keyword_id)
mg(‘products’)->mget_by_category($category_id)
mg(‘products’)->mget_by_brand($brand_id)
mg(‘products’)->get_by_sku($sku)
mg(‘products’)->get_title($product_id)
mg(‘products’)->get_title(‘sku’, $sku)
mg(‘products’)->map_keyword($keyword_id)
mg(‘products’)->colnames()
mg(‘categories’)->get_products($category_id)
So, here’s the class:
class products extends dbentity {
function references() {
return array('brand_id' => 'brands‘, 'category_id' => 'categories');
}
function mappings() {
return array(
‘keywords' => array(‘product_keywords', 'product_id', ‘keyword_id', ‘keywords‘)
, ‘related_products' => array('product_relations', 'from_product_id', 'to_product_id', 'products')
, 'suppliers' => array('supplier_prices', 'product_id', 'supplier_id', 'suppliers‘
, array('wholesale_price' => 'price', 'sku' => 'sku'))
);
}
}
Customizing the sql:
$q = new dbquery(array(
‘select’ => ‘p.name’
, ‘from’ => ‘products p’
, ‘ijoin’ => array(‘c’ => rba::_(‘table’, ‘categories’
, ‘on’, ‘c.id = p.category_id’))
, ‘where’ => “name like ‘%lemon%’ ”
, ‘order_by’ => ‘p.name’
));
$q->add_select(‘p.subname’)
$q->add_ijoin(…)
$q->set_limit(…)
$q->load($q_spec)
Customizing the SQL
$dbquery = array(
‘select’ => ‘c.name as category_name’
, ‘ijoin’ => array(‘c’ => rba::_(‘table’, ‘categories’
, ‘on’, ‘c.id = p.category_id’)))
mg(‘products’)->get(product_id, compact(‘dbquery’))
Zachman Framework
Data
(What)
Activities
(How)
Network
(Where)
People
(Who)
Timing
(When)
Motivation (Why)
Objectives/
Scope
List of
Important
Things
List of
Processes
Business
Locations
Organizational
Units
Business
Events,
Cycles
Business Vision
and Mission
Business
Owner’s View
Terms,
Definitions
Business
Process
Model
Operations
by Business
Location
Org.
Chart,
Roles
Master
Business
Schedule
Business
Policies and
Rules
Architect’s View*
Entity/
Relationship
Diagram
Essential
Functions
Data Links,
Processing
Locations
Roles+Data
(Use
Cases)
State/
transactions,
ELH
Business Rule
Model
Designer’s**
View
Tables,
Classes
System
Design
Network
Architecture
(h/w, s/w types)
User
Interface,
Security
“Control
Flow”
diagrams
Builder’s View***
Data,
physical
storage
design
Detailed
Program
Design
Network
Construction
Screens,
Security
Design
Timing
Definitions
Functioning
System
*
**
***
Working System
John Z calls this the “information designer’s” perspective.
He calls this the “builder’s” view.
This is John Z’s “sub-contractor’s” view.
Rule Design
Rule
Specification