Domeinen in Oracle-applicaties

Nieuws

Niet meer telkens de bekende types definiëren

Bepaalde datatypes, zoals een postcode, een bedrag, een Boolean (J/N of 1/0 veld), een code met verplicht alleen hoofdletters of een datum zonder tijd komen vaak voor in de tabeldefinities bij administratieve applicaties. In dit artikel bespreken we het gebruik van domeinen om niet telkens de bekende types te definiëren.

In Oracle is het de gewoonte om deze definities telkens te definiëren in de bekende types zoals varchar2(6), number(10,2), varchar2(1) of date. Dus als een postcode bijvoorbeeld tien keer voorkomt in de database wordt even vaak gedefinieerd dat deze van het type varchar2(6) is en mogelijk met telkens de constraint dat deze uit 4 cijfers en 2 hoofdletters bestaat. Voor de datum (zonder tijd) wordt telkens aangegeven dat het tijdsdeel ‘00:00:00’ moet zijn en voor de code dat deze alleen hoofdletters mag bevatten, zoals in onderstaand voorbeeld.

Of, iets minder strikt:

Met goede insert:

En een foutieve insert:

Als bij dit soort kolommen telkens opnieuw het datatype en de constraints moeten worden gedefinieerd, dan leidt dit zeker tot veel extra werk en vaak ook tot inconsistenties en/of onvolledige definities. Nog vaker zullen de datatypes en constraints over systemen heen afwijken met problemen bij het uitwisselen van gegevens tussen deze systemen, bijvoorbeeld:

Een naam is de ene keer als 20 de andere keer als 36 lang gedefinieerd;
Een bedrag is de ene keer als number(10,2), de andere keer als number(12,2) gedefinieerd;
Een boolean is de ene keer als number (1), de andere keer als een varchar2(1) gedefinieerd, mogelijk zonder verdere controle op de inhoud;
Een datum waarbij de tijd altijd 00:00:00 moet zijn (ontvangstdatum van een brief of belastingaangifte) staat toch toe om een tijd op te slaan.

Om dit probleem met inconsistenties te voorkomen is het beter om hiervoor definities vast te leggen. Hiervoor bestaan de volgende alternatieven:

Het create type statement in Oracle;
Het create domain statement zoals dat in de ANSI
standaards is gedefinieerd;
Het definiëren van de types of domeinen in een data
dictionary.

We zullen hieronder deze alternatieven nader onderzoeken. Wat zouden we hierbij willen? Wat zijn de eigenlijk de wensen of requirements voor de type- of domein- definities? Dit zijn:

Eenmalig definiëren van deze types en hergebruik van deze definities in de kolomdefinities van de tabellen;
Inclusief constraints, zoals: Bij de datum moet de tijd ‘00:00:00’ zijn; De postcode moet het formaat 9999AA hebben;
De insert, update, delete en select statements blijven hetzelfde (ofwel: even eenvoudig) als ze waren.

Het create type statement

Oracle kent al het begrip create type. Hieronder is dit uitgewerkt met een aantal types en een tabel waarin deze worden gebruikt.

Of:

Omdat het PL/SQL betreft moet na elke statement nog een ‘/’ komen!
Het telefoontype kan een lijst met 1 tot 5 telefoonnummers bevatten. Het adrestype bevat hier 4 attributen. Het definiëren van constraints, zoals hierboven per kolom, is bij het type zelf niet mogelijk.
Deze types kunnen weer in een tabeldefinitie worden gebruikt, bijvoorbeeld een tabel klant, met attributen:

klantnummer (is ook primary key),
klantnummer van de moederorganisatie (is een foreign key naar dezelfde tabel),
klanttype (zakelijk of particulier),
naam, adres,
een lijstje van maximaal 5 telefoonnummers,
maximum en huidig saldo,
systeemdatum dat deze klant is aangemaakt,
datum van de eerste en laatste aankoop.

Het inserten van een record in de tabel klant ziet er nu als volgt uit:

En het select statement ziet er uit als:

Of:

We willen nu het bovenstaande bedrag maal 2 uit de tabel queriën. Helaas gaat dit niet zo gemakkelijk meer:

We moeten daarvoor met een functie uit het type het betreffende bedrag halen:

Dit werkt, maar handig is het niet echt. Mogelijk wordt het met een view allemaal wat bruikbaarder nadat we eerst voor alle types ook zo’n functie aan het type hebben toegevoegd?

De tabel en de view zien er nu zo uit:

Een andere beperking van deze object types is dat deze niet in een primary key of index mogen staan. Dit betekent dat alle primary keys (en dus ook de foreign keys die intrinsiek van hetzelfde type zijn) niet eenvoudig als type kunnen worden gedefinieerd:

Erg handig is dit niet. Om eenduidige definities voor alle kolommen, zoals een bedrag, een boolean, een postcode, een naam en een datum, te krijgen lijkt het Oracle object ‘type’ niet erg geschikt.

Het is zeker wel bruikbaar voor een enkel specifiek datatype, zoals SDO_GEOMETRY in Oracle Spatial, maar voor de meeste kolommen zullen we in Oracle toch weer terugvallen op de bekende datatypes NUMBER, VARCHAR2, DATE, CLOB en BLOB.

De ANSI SQL standaard

De ANSI SQL standaard kent het begrip domein! Deze definitie is ook geïmplementeerd in PostgreSQL. Hierbij kunnen domeinen met constraints en default waarden gedefinieerd worden. Deze worden weer hergebruikt in tabeldefinities zonder bovenstaande overhead en complexe syntax bij insert en update.
De syntax is dan:

Voor het hiervoor gebruikte voorbeeld zou dit er uit kunnen zien als:

De tabel KLANT kan dan worden gedefinieerd met:

De insert, update, delete en select statements blijven geheel identiek aan deze statements zoals we ze kennen bij gebruik van de datatypes number, varchar2 of date. Ook de definitie van de primary key, foreign key of index wijzigt niet. Maar wel is er een grote hoeveelheid redundantie uit de definitie van het schema gehaald. Dat beogen we bij onze data, waarom dan niet ook bij de metadata?

Zoals gezegd, bij PostgreSQL is deze ANSI Standaard al geïmplementeerd. Daarnaast kent PostgreSQL ook een create type voor complexere objecten zoals in Oracle. Sybase en Micrsoft SQL Server hebben met sp_addtype een alternatief voor de domeinen maar met de beperking dat hier geen constraints mogelijk zijn.

Alleen in een repository

De hiervoor genoemde redundantie in de metadata kan ook worden voorkomen door gebruik te maken van een data dictionary of repository waarin de definitie van het schema geheel wordt vastgelegd. Door in de repository gebruik te maken van domeinen of datatypes wordt de definitie daar eenmaal vastgelegd. Bij het genereren van de tabeldefinities voor Oracle wordt deze typedefinitie voor elke kolom uitgegenereerd. Dit is gegenereerd (en wordt bij wijzigingen opnieuw gegenereerd) waardoor in elk geval het extra werk en de inconsistenties vermeden worden. Onder anderen biedt Power Designer deze mogelijkheid.

Conclusie

Door het introduceren van domeinen kan een grote hoeveelheid redundantie uit de definitie van het schema gehaald worden. Het vermeiden van redundantie beogen we al bij onze data, waarom dan niet ook bij de metadata?

Het zou dan ook een grote aanwinst voor Oracle zijn als in de volgende versie een domeindefinitie mogelijk is zoals gedefinieerd in de ANSI standaards. Daarnaast blijven de bestaande object types in Oracle nuttig voor zeer complexe objecten zoals SDO_GEOMETRY in Oracle Spatial. Deze types zijn echter niet geschikt voor de definitie van standaard kolommen in verband met de beperkingen (geen indexen en keys) en de complexe syntax bij queries.

-Toon Loonen

Toon Loonen is werkzaam bij Capgemini en gespecialiseerd in (logisch en fysiek) gegevensmodellering.
Hij is bereikbaar via e-mail: toon.loonen@capgemini.com of toon.loonen@inter.nl.net.