... a database is an organized collection of data.
A relational database organizes data into tables.
(Yarger, Reese, King: MySQL & mSQL; Beijing etc.: O'Reilly, 1999; p.5)

 

IptcDB - die Datenbank

 

Im folgenden soll kurz beschrieben werden, wie die Struktur der Datenbank entwickelt wurde, die dann vom iptcd Dämon verwendet wird, um die eingehenden Nachrichten zu speichern.

Vorbereitungen

Beim Entwurf einer Datenbank-Struktur kommt an erster Stelle die Zusammenstellung der Anforderungen, denen sie gerecht werden soll, v.a.:

Zur Klärung des ersten Punktes müssen die Ausgangsdaten, deren Struktur und intendierte Bedeutung herangezogen werden. Hierbei handelt es sich um die vom International Press Telecommunications Council (IPTC) 1995 festgelegte Format TEC 7901 R5, das im wesentlichen die folgenden Datenfelder vorgibt:

Name Beispiel Erklärung
Source Identification byn one, two or three alphas
Message Number 0178 three or four numerals
Priority of Story 2 one numeral (1-6)
Category of Story pol one, two or three alphas
Word Count 195 one to four numerals
Optional Information any characters up to 50 characters (optional)
Keyword/Catch-Line any characters up to 69 characters
Message Text Text Text
Date and Time 071045 six numerals (two each for day, hour, minute)
Time Zone GMT three alphas (optional)
Month of Transmission jan three alphas (optional)
Year of Transmission 91 two numerals (optional)

Bei diesen Angaben ist nun zu überlegen, welche von ihnen potentiell mehrfach (also in verschiedenen Nachrichten) vorkommen können, und welche (bzw. welche Kombination von ihnen) bei jeder Nachricht originär, einzigartig sind. Mögliche Kandidaten für mehrfaches Vorkommen sind hier:

Source Identification: der Name der SenderIn

Diese ein bis drei Zeichen lange Kennung kommt in allen Nachrichten derselben SenderIn vor. Zudem ist davon auszugehen, daß die NutzerInnen der zu entwerfenden Datenbank auch gezielt nach genau diesem Datum suchen werden oder es als zusätzliches Auswahl-Kriterium komplexerer Abfragen verwenden.

Priority of Story: die Nachrichten-Priorität

Dieselbe Prioritäten-Angabe kann in mehreren Nachrichten erscheinen, auch unabhängig von der jeweiligen SenderIn.

Category of Story: die Nachrichten-Kategorie

Auch die Kategerie kann, unabhängig von der jeweiligen SenderIn, in mehreren Nachrichten erscheinen.

Optional Information: optionale Angaben zur Beschreibung der Nachricht

Was unter optionalen Angaben konkret zu verstehen ist, wird in der IPTC Format-Definition nicht exakt festgelegt; sofern sie sich indes in der Praxis als "de facto" standadisiert erwiesen, könnten auch sie in mehreren Nachrichten vorkommen.

Keyword: Stichwort zur Beschreibung der Nachricht

Das Stichwort soll eine Nachricht kurz beschreiben; damit ein solches Wort überhaupt Sinn hat, müßte es einem genormten Wortschatz (ein sogenannter Thesaurus) entnommen sein. Jedenfalls aber kann dasselbe Stichwort zur Kennzeichnung unterschiedlicher Nachrichten verwendet werden.

Durch die Analyse von Echtdaten stellte sich - wie so oft - heraus, daß Theorie (die Format-Definition) und Praxis (die tatsächlich empfangenen Daten) nicht, hm, sagen wir: unbedingt deckungsgleich sind. So verwenden beispielsweise manche Nachrichten-Agenturen verschiedene Sender-Kennungen oder es werden anstelle eines Stichwortes derer mehrere angegeben, wobei erschwerend hinzukommt, daß diese ganz offenbar rein willkürlich vergeben werden und durchaus nicht immer einen ersichtlichen Sinn haben (bspw. Zeit-Angaben wie 16:00 Uhr).

Sellt man nun neben diese vorläufigen Ergebnisse die möglichen und intendierten Datenbank-Abfragen der NutzerInnen sowie schließlich auch Erwägungen zu den elektronischen Kosten (Plattenplatz- und Arbeitsspeicher-Verbrauch der Datenbank-Dateien bzw. des DBMS während der Abfragen, so ist das Rüstzeug beisammen, um sich konkret, nicht mehr bloß vorbereitend, mit dem Entwurf der Datenbank zu befassen.

Datenbank Design

Ausgangspunkt ist nach wie vor die oben zusammengefaßte IPTC Format-Definition. Die einzelnen Bestandteile einer Nachricht wurden im Rahmen der sogenannten Normalisierung erst zu Nachrichten-Attributen zusammengestellt und dann in Form sog. Entitäten und ihrer Beziehungen abgebildet:

 Abb.: Entity Relationship Model
Die Beziehungen der beteiligten Entitäten

 Abb.: Entity Relationship Symbol-Legende
Symbol-Legende

Dieses ER-Modell leitet direkt weiter zur Darstellung der daraus entstehenden Relationen. Sie sind die letzte Vorstufe zur Festlegung der Datenbank-Struktur (Tabellen, Indizes etc.) Wie zu sehen ist, haben wir nun insgesamt fünf Entitäten, wobei von den vorhin diskutierten Kandidaten die Datenfelder Sender, Category und Keywords tatsächlich zu eigenen Entitäten geworden sind, während bei der Priority dagegen entschieden wurde, weil es sich um ein einfaches numerisches Datum handelt, bei dem (Platten-)Platz Verbrauch und die Komplexität bei einer Abfrage zu hoch erschienen, würde sie als eigene, separate Entität behandelt. Die Optional Information hingegen erwiesen sich bei der Untersuchung der vorliegenden Echtdaten als zu beliebig, um sie zu separieren.

Bei den Entitäten Sender und Category ist jeweils ein Attribut namens Name zu sehen, das so in der IPTC Format-Definition nicht vorkommt. Es ist gedacht als Feld, in dem die echte (ausführliche) Bezeichnung der jeweiligen SenderIn bzw. der jeweiligen Kategorie abgelegt werden kann, um so etwas hübschere Berichte und Übersichten erstellen zu können.

Neu hinzugekommen ist indes die Entität namens MsgText, wohinter sich, wer hätte das gedacht?, der tatsächliche Text (Inhalt) der jeweiligen Nachrichten verbirgt. Die Begründung für diese Abkoppelung des Nachrichten-Inhaltes von den anderen Angaben liegt in einer Kombination unterschiedlicher Erwägungen. Ein Punkt dabei ist die Datensicherheit bzw. Datensicherung: durch die Aufteilung in zwei Tabellen (und damit entsprechend viele Dateien, zuzüglich der jeweiligen Index-Dateien) lassen sich diese leichter sichern als eine alles enthaltene (und entsprechend größere) Datei. Ein anderer Punkt betrifft die Frage der späteren Datenbank-Abfragen durch die NutzerInnen: Durch die Separierung des Nachrichten-Inhaltes (der im Volltext durchsuchbar ist) können die SQL Anweisungen einfacher und präziser formuliert werden. Zudem kann das DBMS bei jenen Abfragen, die sich nur auf die Felder der Entität News beziehen (also den gewissermaßen bibliographischen Angaben zu einer Nachricht), das vergleichsweise riesige Feld (und dessen Index) des Nachrichten-Inhaltes komplett ignorieren.

Überführen wir nun diese symbolisch dargestellten logischen Zusammenhänge in ein Modell ihrer Beziehungen, so gelangen wir zu der folgenden Darstellung:

 Abb.: das Relationen Modell
Das entstandene Relationen Modell

Datenbank Modell

Wir haben nun also im Grunde bereits eine Liste jener Tabellen, aus denen die Datenbank bestehen wird, einschließlich ihrer Verknüpfungen durch gleiche Daten-Felder (MID, CID, SID, WID). Daraus ergibt sich ein Datenbank-Modell, das (auf Tabellen-Ebene, also ohne Attribute/Felder) wie folgt dargestellt werden kann:

 Abb.: ein Datenbank Diagramm
Ein Datenbank Diagramm

Hier ist ersichtlich, wie von jeder einzelnen Entität (jetzt: Tabelle) aus jede andere erreicht werden kann.

Man sieht: Alles ist einerseits fein säuberlich aufgeteilt und geordnet, zugleich aber ist andererseits jedes Datum auch über maximal zwei Tabellen-Verknüpfungen erreichbar, wobei der quantitativ weitaus überwiegende Teil der zu erwartenden Datenbank-Abfragen keinerlei Tabellen-Verknüpfungen erfordert: wenn schnell eine Übersicht der eingegangenen Nachrichten benötigt wird (etwa mithilfe der News.DateTime oder News.Priority Felder).

Datenbank Erstellung

Ausgehend von diesen Relationen kann nun ein SQL Script formuliert werden, das die benötigten Tabellen und Indizes der Datenbank erzeugt (getestet mit MySQL v4.0.{15,16,17,18}):
$  mysql <CreateDB.sql


CREATE DATABASE IF NOT EXISTS IptcDB;

USE IptcDB;

# Table structure for table 'tNews'
DROP TABLE IF EXISTS tNews;
CREATE TABLE tNews (
    fMID VARCHAR(28) NOT NULL,
    fDateTime DATETIME NULL default '0000-00-00 00:00:00',
    fPriority ENUM('0', '1', '2', '3', '4', '5', '6') NOT NULL,
    fMsgNum SMALLINT(4) UNSIGNED ZEROFILL NOT NULL,
    fWordCount SMALLINT(5) UNSIGNED ZEROFILL NOT NULL,
    fCatchline VARCHAR(255) default '', -- optional field
    fOptInfo VARCHAR(50) default '',    -- optional field
    PRIMARY KEY (fMID),
    FULLTEXT ixNewsCatchline (fCatchline),
    KEY ixNewsDateTime (fDateTime),
    KEY ixNewsPriority (fPriority),
    KEY ixNewsMsgNum (fMsgNum),
    FULLTEXT ixNewsOptInfo (fOptInfo),
    KEY ixNewsWordCount (fWordCount)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='IPTC messages (head/foot)';

# Table structure for table 'tText'
DROP TABLE IF EXISTS tText;
CREATE TABLE tText (
    fMID VARCHAR(28) NOT NULL,
    fText LONGTEXT NOT NULL,
    PRIMARY KEY (fMID),
    FULLTEXT ixTextText (fText)
) TYPE=MyISAM PACK_KEYS=DEFAULT COMMENT='IPTC messages (body)';

# Table structure for table 'tCategory'
DROP TABLE IF EXISTS tCategory;
CREATE TABLE tCategory (
    fCID CHAR(3) NOT NULL,
    fCName VARCHAR(255) default NULL,
    PRIMARY KEY (fCID),
    UNIQUE KEY ixCategoryCName (fCName)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='used categories';

# Table structure for table 'tNewsCategory'
DROP TABLE IF EXISTS tNewsCategory;
CREATE TABLE tNewsCategory (
    fMID VARCHAR(28) NOT NULL,
    fCID CHAR(3) NOT NULL,
    KEY ixNewsCategoryMID (fMID),
    KEY ixNewsCategoryCID (fCID)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='relation tNews<=>tCategory';

# Table structure for table 'tSender'
DROP TABLE IF EXISTS tSender;
CREATE TABLE tSender (
    fSID CHAR(3) NOT NULL,
    fSName VARCHAR(255) default NULL,
    PRIMARY KEY (fSID),
    KEY ixSenderSName (fSName)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='sender (news agency)';

# Table structure for table 'tNewsSender'
DROP TABLE IF EXISTS tNewsSender;
CREATE TABLE tNewsSender (
    fMID VARCHAR(28) NOT NULL,
    fSID CHAR(3) NOT NULL,
    KEY ixNewsSenderMID (fMID),
    KEY ixNewsSenderSID (fSID)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='relation tNews<=>tSender';

# Table structure for table 'tKeyWords'
DROP TABLE IF EXISTS tKeyWords;
CREATE TABLE tKeyWords (
    fWID BIGINT UNSIGNED NOT NULL auto_increment,
    fWord VARCHAR(64) NOT NULL,
    PRIMARY KEY (fWID),
    UNIQUE KEY ixKeyWordsWord (fWord)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='used keywords';

# Table structure for table 'tNewsKeys'
DROP TABLE IF EXISTS tNewsKeys;
CREATE TABLE tNewsKeys (
    fMID VARCHAR(28) NOT NULL,
    fWID BIGINT UNSIGNED NOT NULL,
    KEY ixNewsKeysMID (fMID),
    KEY ixNewsKeysWID (fWID)
) TYPE=MyISAM PACK_KEYS=1 COMMENT='relation tNews<=>tKeyWords';

FLUSH TABLES;   -- write all to disk

SET sql_log_off=1;  -- disable logging of username/passwords

-- set new privileges of default users for this DB
-- (Note, that this are database users, not system/shell users!)
-- writing user:
GRANT Select on IptcDB.* to 'iptcw'@'localhost' IDENTIFIED BY 'iptcw';  -- set dummy
REVOKE ALL on IptcDB.* from 'iptcw'@'localhost';    -- clear all previous priviledges
GRANT Select,Insert,Update,Delete,Lock Tables
    on IptcDB.* to 'iptcw'@'localhost' IDENTIFIED BY 'iptcw';   -- set 'real'

-- reading user:
GRANT Select on IptcDB.* to 'iptcr' IDENTIFIED BY 'iptcr';
REVOKE ALL on IptcDB.* from 'iptcr';
GRANT Select on IptcDB.* to 'iptcr' IDENTIFIED BY 'iptcr';

FLUSH PRIVILEGES;   -- write all to disk

SET sql_log_off=0;  -- enable logging again

Ein paar knappe Bemerkungen zu den hier angewandten Konventionen und Erwägungen:

 Abb.: Tabellen / Relationen
Tabellen / Relationen


[... more to come eventually ...]