Practical Object Oriented Models In Sql

Practical object-oriented models in SQLBill KarwinPostgreSQL Conference West 09 • 2009/10/17Me• 20+ years experience• Application/SDK developer• Support, Training, Proj Mgmt• C, Java, Perl, PHP• SQL maven• MySQL, PostgreSQL, InterBase• Zend Framework• Oracle, SQL Server, IBM DB2, SQLite• Community contributorObject-Oriented vs. Relational• Impedance mismatch• OO operates on instances; RDBMS operates on sets• Compromise eitherOO strengths or relational strengthsExample databaseEntity-Attribute-ValueIf you try and take a cat apart to see how it works, the first thing you have on your hands is a non-working cat.— Richard DawkinsEntity-Attribute-Value• Objective: extensibility• Variable sets of attributesbug_idbug_typepriority descriptionseveritysponsorcrashes when loss of 1234BUGhighsavingfunctionality3456FEATURElowsupport XMLAcme Corp.Entity-Attribute-Value Meta-TableCREATE TABLE eav (bug_idINT NOT NULL,attr_nameVARCHAR(20) NOT NULL,attr_valueVARCHAR(100),PRIMARY KEY (bug_id, attr_name),FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id));mixing data with metadataEntity-Attribute-ValueWhat does this look like?bug_idattr_nameattr_value1234priorityhigh1234descriptioncrashes when saving1234severityloss of functionality3456prioritylow3456descriptionsupport XML3456sponsorAcme Corp.Entity-Attribute-ValueProblems: namesbug_idattr_nameattr_value1234created2008-04-013456created_date2008-04-01Entity-Attribute-ValueProblems: valuesbug_idattr_nameattr_value1234created_date2008-02-313456created_datebanana