[Oracle] Kurzüberblick Objektrelationaler DB Ansätze

[Exceptionfault]

Ein wunderschönes Feature, welches man als Anwendungsentwickler in Verbindung mit Datenbanken unbedingt kennen sollte lautet "Objektrelationales Datenbankdesign". Ich muss gestehen, dass ich nicht beurteilen kann, wie gut die Möglichkeiten in anderen DBM Systemen wie DB2 oder MS-SQL Server umgesetzt sind. In Oracle bieten sich jedoch meiner Meinung nach wunderbare Möglichkeiten, welche viel zu selten eingesetzt werden.

Zunächst eine kleine Erläuterung der Unterschiede zwischen "normalen" relationalen Datenbankmodellen und "objektrelationalen Datenbankmodellen". Die meisten DB Benutzer kennen, wenn sie z.B. mySQL verwenden lediglich zweidimensionale Tabellen. Diese werden im Idealfall mit sog. Constraints verknüpft um eine logische Datenintegrität zu erhalten. In einer relationalen Tabelle spiegelt jede Spalte ein einfaches Attribut, z.b. ein Datum wieder, und jede Zeile unterschiedliche Ausprägungen.

Nehmen wir zum einfacheren Verständnis eine kleines Beispiel mit Kundendaten. Wir haben eine Kundentabelle mit Kundennummer und Name. Da Kunden möglicherweise mehrere Kontaktadressen haben, müssen wir eine eigene Tabelle mit Adressen anlegen und per Kundennummer darauf verweisen. Weiter gibt es eine Tabelle Rechnungen, sowie eine Tabelle Rechnungspositionen. Ich lasse in diesem Beispiel bewusst Primary- und Foreign Keys weg um den Code kürzer zu halten.

CREATE TABLE customer (
  CUSTID     NUMBER(10,0),
  FIRSTNAME  VARCHAR2(30),
  LASTNAME   VARCHAR2(30)
)
/

CREATE TABLE addresses (
  ADDID      NUMBER(10,0),
  CUSTID     NUMBER(10,0),
  STREET     VARCHAR2(40),
  ZIP        VARCHAR2(5),
  CITY       VARCHAR2(40)
)
/

CREATE TABLE invoice (
  INVOICEID  NUMBER(10,0),
  ADDID      NUMBER(10,0),
  PURCHDATE  DATE
)
/

CREATE TABLE artsales (
  ARTID      NUMBER(10,0),
  INVOICEID  NUMBER(10,0),
  AMOUNT     NUMBER(10,0)
)
/

Stellen wir uns nun vor, wir möchten eine Auswertung von verkauften Artikeln in Beziehung unseren Kunden durchführen. Allein dafür bräuchten wir einen JOIN auf 4 Tabellen. Das Statement sähe nicht sonderlich hübsch aus, erst recht nicht wenn noch Einschränkungen z.B. nach Postleitzahl Regionen hinzukämen. Schauen wir uns das gleiche Schema als Objektrelationales Modell einmal an. Ziel ist es hier nicht in "flachen, zweidimensionalen" Tabellen, sondern in Objekten zu denken und einen realen Zustand bestmöglich in der Datenbank abzubilden.

In unserem Beispiel bedeutet dies: Wir haben Kunden und Rechnungen. Jeder Kunde kann mehrere Adressen haben. Jede Rechnung hat einzelne Rechnungspositionen und einen eindeutigen Kunden. Um eine 1:n Beziehung abzubilden, z.B. die Adressen der Kunden, brauchen wir eine sog. "Nested Tables". Man kann es sich so vorstellen, dass in der Zelle der Kundentabelle nun eine eigene Tabelle angelegt wird, welche die Adressen des jeweiligen Kunden enthalten. Ebenso verhält es sich mit den Rechnungen und den Rechnungspositionen.

Aber zunächst benötigen wir die Definitionen unserer Objekte:

CREATE OR REPLACE TYPE address_t AS OBJECT (
    ADDID      NUMBER(10,0),
    STREET     VARCHAR2(40),
    ZIP        VARCHAR2(5),
    CITY       VARCHAR2(40)
)
/

CREATE OR REPLACE TYPE addresses_nt AS TABLE OF address_t
/

CREATE OR REPLACE TYPE invoicepos_t AS OBJECT (
    ARTID      NUMBER(10,0),
    AMOUNT     NUMBER(10,0)
)
/

CREATE OR REPLACE TYPE invoicepos_nt AS TABLE OF invoicepos_t
/

CREATE OR REPLACE TYPE customer_t AS OBJECT (
    CUSID      NUMBER(10,0),
    FIRSTNAME  VARCHAR2(30),
    LASTNAME   VARCHAR2(30),
    ADDRESSES  addresses_nt
)
/

CREATE OR REPLACE TYPE invoice_t AS OBJECT (
    INVOICEID  NUMBER(10,0),
    CUSTOMER   REF customer_t,
    ADDID      NUMBER(10,0),
    POSITIONS  invoicepos_nt
)
/

Anhand der Endungen kann man leicht erkennen um welchen Typ es sich bei den Objekten handelt. "_t" steht für eine Typdefinition und "_nt" für Nested Table. "REF" im Typ Rechnung bedeutet Reference und ist quasi wie ein Link auf unseren Kunden. Bis jetzt haben wir nur Typdefinitionen, aber noch nichts worin wir die Objeke auch speichern können. Dazu legen wir nun zwei Tabellen an. Zu jeder Tabelle müssen wir auch definieren wie bzw. unter welchem Namen die Nested Tables gespeichert werden.

CREATE TABLE customer OF customer_t 
    NESTED TABLE ADDRESSES STORE AS all_adresses RETURN AS VALUE     
/

CREATE TABLE invoices OF invoice_t
    NESTED TABLE POSITIONS STORE AS all_invoicepos RETURN AS VALUE
/

Fügen wir nun ein paar Testdaten ein:

INSERT INTO CUSTOMER VALUES ( 1, 'Heinz', 'Mustermann', 
   ADDRESSES_NT (
      ADDRESS_T(1, 'Musterstr. 25', '01234', 'Musterdorf'), 
      ADDRESS_T(2, 'Musterdorfstr. 11b', '98765', 'Musterstadt')
   )
);

INSERT INTO INVOICES VALUES ( 1,
  (SELECT REF(C) FROM CUSTOMER C WHERE CUSID = 1),
   2,
   INVOICEPOS_NT(
     INVOICEPOS_T( 345, 1 ),
     INVOICEPOS_T( 123, 2 ),
     INVOICEPOS_T( 789, 4 )
   )
);

Der Kunde Heinz Mustermann hat also zwei Adressen, und eine Rechnung mit 3 Artikeln welche zur Adresse Nr. 2 bestellt wurden.
Die Insert Statements sehen so seltsam aus, da wir mehrere Tabellen (NT) pro Insert Statement befüllen und außerdem ja nun nicht mehr mit einfachen Datentypen pro Zelle arbeiten, sondern mit Objekten. Insofern sieht das ganze zugegebenermassen etwas komplizierter aus. Es ist auch mehr Code in der Entwicklung der Objekte. Kommen wir nun aber zu der schönen Seite: Den Abfragen!

Wir möchten alle Anschriften von Kunde Nr 1. Hierfür müssen wir in der From Klauses lediglich die Spalte ADRESSES als Tabelle deklarieren.

SELECT FIRSTNAME, LASTNAME, STREET, ZIP, CITY FROM CUSTOMER U, TABLE(U.ADDRESSES) WHERE CUSID = 1;

LASTNAME                      FIRSTNAME                      STREET                                   ZIP   CITY
----------------------------- ------------------------------ ---------------------------------------- ----- -----------
Mustermann                    Heinz                          Musterstr. 25                            01234 Musterdorf
Mustermann                    Heinz                          Musterdorfstr. 11b                       98765 Musterstadt

Sicher, das wäre auch mit einem JOIN noch recht übersichtlich. Jetzt möchten wir alle Artikel und Rechnungsnummern, mit der zugehörigen Lieferadresse und Anrede aller Kunden.

SELECT  VALUE(I).CUSTOMER.LASTNAME,
        VALUE(I).CUSTOMER.FIRSTNAME,          
        A.STREET,
        P.ARTID, 
        I.INVOICEID,
        P.AMOUNT 
        FROM INVOICES I, TABLE(I.POSITIONS) P, TABLE(VALUE(I).CUSTOMER.ADDRESSES) A
        WHERE A.ADDID =I.ADDID;

VALUE(I).CUSTOMER.LASTNAME     VALUE(I).CUSTOMER.FIRSTNAME    STREET                                     ARTID     AMOUNT
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
Mustermann                     Heinz                          Musterdorfstr. 11b                           345     1
Mustermann                     Heinz                          Musterdorfstr. 11b                           123     2
Mustermann                     Heinz                          Musterdorfstr. 11b                           789     4

Und alles klar ? ;-)
Und nun das wirklich tolle. Bisher haben wir trotz der Objekte unsere Statements immer so aufgebaut, dass wir eine zweidimensionale Tabelle zurückbekommen. Warum also der Aufwand mit den Objekten ?
Ganz einfach, weil ich mir in meinem Programm auch direkt die Objekte abrufen kann. Und das gilt nicht nur für die Werte des jeweiligen Objektes. Ich kann sogar Proceduren zu einem Objekt anlegen und ausführen. Da dies hier viel zu weit führen würde nur ein Beispiel, schnell in VBA (Excel) zusammengebastelt:

    Set result = db.CreateDynaset("select value(c) cust from customers c", 0&)
    
    Set customer = result("cust").Value
    Set addr_nt  = customer.addresses
    
    Debug.Print customer.Lastname
    Debug.Print customer.Firstname
    
    For i = 1 To addr_nt.TableSize
      Set addr = addr_nt(i)
      Debug.Print addr.city & "   " & contact.street
      addr.IterNext
    Next i

Das ganze ist ein ziemlich umfangreiches Thema und ich habe hier viele wichtige Aspekte unterschlagen (müssen). Hoffe aber, dass ich trotzdem einen kleine Einblick in die Möglichkeiten geben konnte.