Transcript Object-Oriented Programming & Relational Design

Object-Oriented Programming
& Relational Design
Clash of the Paradigms….
Stuart R Ainsworth
Gladiator Technology Services
atlantaMDF
June 12, 2006
About Me

American Cancer Society
Behavior Research Center
– Research Analyst, 1995-1998
– Project Manager, 1998-2000

Onity
– Report Analyst, 2000-2001
– Database Administrator, 2001-2002
About Me

Gladiator Technology Services, Inc
– Lead Developer, 2002-2003
– Senior DBA, 2003-2005
– Database Developer, 2005-Present
Goals


Introduce basic concepts of OOP
Compare & Contrast OOP and ERD
– Historical
– Design Implications

Discuss Relevance to SQL Pro’s
Goals



Provide Concepts & Code
Design Scenarios
Prognosticate on the future of design
Precepts of Good Design



Isolate database development from
application development.
Encapsulation
Loose Coupling
Layered Design
APPLICATION/INTERFACE LAYER
BUSINESS OBJECTS LAYER
DATA ACCESS LAYER
DATA STORAGE LAYER
History Lesson
“Set the WABAC machine,
Sherman!”
-Mr. Peabody
History-Rel Paradigm

Relational design based on work of E.F.Codd
– A Relational Model of Data For Large Shared
Data Banks – 1970 ACM
– Codd’s 12 Rules for Relational DB’s (1985)

Implementation
– Ingres (1974)
– Relational Software (Oracle; 1979)
History-Rel Paradigm

Context:
– Hierarchical databases prevalent
Tree structure
 Redundant data in attributes

– Expense of computer hardware
Limited storage capability
 Limited expansion possibilities

History-Rel Paradigm
“By the time UNIX began to become popular
(1974), a well configured PDP-11 had 768 Kb of
core memory, two 200 Mb moving head disks
(hard disks), a reel to reel tape drive for backup
purposes, a dot-matrix line printer and a bunch of
[dumb] terminals. This was a high end machine,
and even a minimally configured PDP-11 cost about
$40,000. Despite the cost, 600 such installations
had been put into service by the end of 1974,
mostly at universities.”
History-Rel Paradigm
“In 1973, IBM developed what is considered to be
the first true sealed hard disk drive... It used two
30 Mb platters. Over the following decade, sealed
hard disks (often called Winchester disks) took their
place as the primary data storage medium, initially
in mainframes, then in minicomputers, and finally in
personal computers starting with the IBM PC/XT in
1983.”
History-OOP

Alan Kay
– Smalltalk (1971)
– Biological metaphors; cells in a body
– Building blocks; code reduction
– Recent Work

http://www.squeakland.org/
History-OOP
“Smalltalk (and object-oriented
programming in general) was a way to
achieve these goals. In theory, programs
would be easier to write because they were
modeled on things that were easier to
understand…. Additionally, programs would
be easier to read, and hence, easier to
maintain, vastly reducing the largest costs
associated with software.”
Historical Comparison

Relational

OOP
– Optimize data
storage
– Optimize data
retrieval
– Optimize
development time
– Optimize application
support
Data-centric efficiency
Coder-centric efficiency
O/R Impedence
“The object-oriented paradigm is based on proven
software engineering principles. The relational
paradigm, however, is based on proven
mathematical principles. Because the underlying
paradigms are different the two technologies do not
work together seamlessly. The impedance
mismatch becomes apparent when you look at the
preferred approach to access: With the object
paradigm you traverse objects via their
relationships whereas with the relational paradigm
you join the data rows of tables. ”
http://www.agiledata.org/essays/impedanceMismatch.html
OOP Coding Concepts
All programmers are
playwrights and all
computers are lousy actors.
-Unknown
Definitions

Class
A storage medium for keeping size,
structure, and operations for the type.
Public Class Book
Public Text as String = “”
Public PageLength as Integer = 10
End Class
Definitions

Object
Instance of class; Run-time value that
stores state of a class
Dim fairyTales as Book
fairyTales = New Book()
fairyTales.Text = “Once upon a time…”
fairyTales.PageLength = 8
Definitions

Methods
Verbs associated with classes; actions
performed by objects
Public Class Book
Public Function GetPage…
End Class
Dim TextOnPage6 as String = “”
TextOnPage6 = fairyTales.GetPage(6)
Definitions

Properties
Attributes of class; descriptive
Public Class Book
Public Property Title()as String…
Get…
Set…
End Class
fairyTales.Title = “Goldilocks”
Definitions

Events
A signal that an action (method) has
occurred. Events are handled.
Public Class StartEventArgs
Inherits System.EventArgs
'Provide constructors, fields and
'accessors for the arguments.
End Class
Public Class Sender
Public Event Start(ByVal sender As Object, ByVal e As
StartEventArgs)
Protected Overridable Sub OnStart(ByVal e As StartEventArgs)
RaiseEvent Start(Me, e)
End Sub
'...
End Class
Public Class Receiver
Friend WithEvents MySender As Sender
Private Sub MySender_Start(ByVal sender As Object, _
ByVal e As StartEventArgs) Handles MySender.Start
'...
End Sub
End Class
Definitions


Encapsulation
Internal functionality of object is
hidden from callers. “Black box”
programming.
Loose coupling
No assumptions between applications
or objects
Definitions


Encapsulation
Internal functionality of object is
hidden from callers. “Black box”
programming.
Loose coupling
No assumptions between
applications or objects
Definitions

Hierarchy
Superclasses & subclasses; objects
relate to each other in a hierarchical
fashion (e.g., ducks are birds, which
are animals, which are organisms…)
Definitions


Inheritance
Process by which a subclass is derived
from a superclass; methods in
particular are inherited.
Ducks Fly() because Birds Fly(), and
Ducks inherit from Birds
Definitions


Inheritance
Process by which a subclass is
derived from a superclass;
methods in particular are
inherited.
Ducks Fly() because Birds Fly(), and
Ducks inherit from Birds
Definitions


Overriding
Subclass methods are used before
Superclass methods.
Polymorphism
Common method across different
objects with different implementations.
Ducks Swim(), Fish Swim()
Where are we?



OOP Developers are bored.
SQL Developers are challenged.
DBA’s are wondering how the systems
are doing back at the office.
Common OOP/RP Clashes
Computers are useless. They
can only give you answers.
-Pablo Picasso
Five Common Issues





Data Access Layer Debate
Inheritance Design
Iteration
Triggers
CLR misconceptions
Layered Design
APPLICATION/INTERFACE LAYER
DataSets
BUSINESS OBJECTS LAYER
ADO.NET
Stored Procs
TABLES
DATA ACCESS LAYER
DATA STORAGE LAYER
DAL Debate

Role of Database in DAL
– Stored Procedures
Security
 Typically Parameterized
 Encapsulation

DAL Debate

Role of OOP in DAL
– Transformation from data to data objects

ADO.NET, ADO, RDO
– Parameterized SQL
Sometimes necessary
 Developers must collaborate with DBA
 Raises encapsulation concerns

Five Common Issues





Data Access Layer Debate
Inheritance Design
Iteration
Triggers
CLR misconceptions
Inheritance in ERD


Entity: some unit of data that can be
classified and have stated relationships
to other entities.
Like objects, entities are nouns:
– “We sell cars and trucks.”
– Entities DO NOT INHERIT from entities
Can be emulated
 Emulation <> reality

Inheritance in ERD

Vertical Mapping
– 1-1 Joins; Common Attributes grouped as
a single entity.

Horizontal Mapping
– Classic ERD; Entities=Tables, regardless
of inheritance.

Filter Mapping
– Single Parent Entity; allow NULL values
Vertical Integration
PEOPLE
PK
ID
FirstName
LastName
CUSTOMERS
EMPLOYEES
PK,FK1 ID
PK,FK1 ID
DateOfFirstPurchase
Department
Horizontal Integration
PEOPLE
CUSTOMERS
EMPLOYEES
PK
ID
FirstName
LastName
Department
PK
ID
FirstName
LastName
DateOfFirstPurchase
Filtered Integration
PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
Filtered Integration
PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
EMPLOYEES
Filtered Integration
PEOPLE
PK
ID
FirstName
LastName
Department
DateOfFirstPurchase
CUSTOMERS
Deciding Factors

How does your business define
entities?
– People vs Customers/Employees
What will you report on most?
 NULL’s allowed or Not?

– Design impacts performance
Alternate Example: Car Dealership
 Alternate Solution: XML

Over-Normalization

1NF
– Primary Key
– Remove duplicate columns

2NF
– Remove subsets of data
– Foreign Key relationships

3NF
– Remove columns independent of primary key
Over-Normalization

Normalization stops at the Entity level
– NO CONCEPT OF INHERITENCE IN SQL

Temptation is to have a “root” entity
– Use 1-to-1 JOINS for all other entities
OBJECTS (ID, name, description)
 CAR (ID, WheelID)
 WHEELS (ID)

Over-Normalization
SELECT o1.name, o1.description,
o2.name, o2.description
FROM Cars c JOIN Objects o1 ON c.ID=o1.ID
JOIN Wheels w ON c.WheelID=w.ID
JOIN Objects o2 ON w.ID=o2.ID
SELECT c.name, c.description,
w.name, w.description
FROM Cars c JOIN Wheels w ON c.WheelID=w.ID
Five Common Issues





Data Access Layer Debate
Inheritance Design
Iteration
Triggers
CLR misconceptions
Iteration

In OOP design, the base unit is the
object
– A set is represented by a collection
DataTable is collection of DataRows
 DataSet is collection of DataTables

– Property inspection is iterative
Row(0) to Rows.Count-1
 Data Retrieval: Geographic orientation

– Row(100), MoveNext
Iteration

In ERD, the base unit is the table
– A set is represented by the table
A row is a set of 1, which is a subset of the
containing table
 Data is filtered by JOINS and WHERE clause

– Tables are order-less
No Row(0)
 Data Retrieval: Content Orientation

– WHERE id = 1
Iteration

In ERD implementations, iteration
should be nominal
– Cursors are performance killers
– WHILE loops should be unnecessary

SQL is declarative language
– Things happen “all-at-once”
Iteration

Typical Iterative Example
– Complex business logic affecting one row
of data at a time
– Cursor calls that stored procedure for
every row in a table
Five Common Issues





Data Access Layer Debate
Inheritance Design
Iteration
Triggers
CLR misconceptions
Triggers

Most OOP developers love triggers
– Analogous to Event handling

Most SQL developers dislike triggers
– Keyhole Drill Bit; sometimes you HAVE to
use them.

Most DBA’s hate triggers
– Voodoo design.
Five Common Issues





Data Access Layer Debate
Inheritance Design
Iteration
Triggers
CLR misconceptions
CLR Concerns

SQL 2005 – Embedded CLR
– OOP Developers reuse business objects in
app code; why not SQL CLR?
Different purposes (lower Layers of Design)
 Objects in database are NOT part of GAC

– SQL CLR belongs to database
– Akin to XCOPY deployment
– Object synchronicity issues
CLR Concerns

.NET Stored Procedures
– Slower than T-SQL
– Yet to find a good example of why they
are necessary
UDF Functions for formatting
 Better system wide data collection
 String munging

Perspectives
The future will be better
tomorrow.
-Dan Quayle
Future Technologies

LINQ
– SQL-like syntax for heterogeneous
datasources; Apps would become
datasource-agnostic
– Database is an object; Tables are objects

O/RM
– Thin layer between ADO.NET and
application, mapping objects to entities
Design Perspectives

Rod Paddock – Wither T-SQL
– LINQ & CLR need to replace T-SQL

Jim Gray – Databases as Objects
– Business logic moved to data storage

Adam Machanic – SP Interfaces
– Stored Procedures act as OOP interfaces

Paul Nielsen – Nordic O/R design
– OOP emulation in T-SQL
Layered Design
APPLICATION/INTERFACE LAYER
BUSINESS OBJECTS LAYER
DATA ACCESS LAYER
DATA STORAGE LAYER
Questions?
Nothing endures but
change.
-Heraclitus