Maintainer: Stefan Meretz, Version 1, 16.05.2000	Druckversion
Projekt-Typ: halboffen	Tipp: Wer eingeloggt ist, kann eigene Kommentare korrigieren. Einloggen können sich Mitglieder
Status: Aktiv

(1) ASCII-Grafik (genaue Definitionen unten). Anmerkungen: MySQL kennt keine Foreign-Keys, trotzdem tue ich mal so. Lies "Metatext" als "Project".

(2)

###########################       ist         ###########################
#         MEMBER          #     Projekt-      #         PROJMEMB        #
###########################  1  mitglied   n  ###########################
# P memb_id  int unsigned #>-+---------------># P memb_id  int unsigned #
#   last_n   varchar(50)  #  |         n +---># P proj_id  int unsigned #
#   first_n  varchar(30)  #  |           |    ###########################
# I email    varchar(80)  #  |           |
#   passwd   varchar(50)  #  |           |
#   subscr   datetime     #  |           |
###########################  |           | hat Projekt-
                             |           | mitglieder
      ist Maintainer         |           |
 +---------------------------+           |
 |                                       |       hat Sub-Projekte
 |  +------------------------------------|---------------------------------+
 |  |                                    |                                 |
 |  |      wird referenziert von         |                                 |
 |  +--------------------------------+   |                                 |
 |  |                                |   |                                 |
 |  |  ###########################   |   |    ###########################  |
 |  |  #        METATEXT         #   |   |    #         SUBPJS          #  |
 |  |  ########################### 1 |   |    ###########################  |
 |  +-># P proj_id  int unsigned #>--|---+---># P proj_id  int unsigned #  |
 |  n  # P vers_id  int unsigned #   |   |  n # P subpj_id int unsigned #>-+
 +---->#   memb_id  int unsigned #   |   |    ###########################  1
 |  n  #   title    varchar(255) #   |   |
 |     #   titlabb  varchar(15)  #   |   |
 |     #   submit   datetime     #   |   |
 |     #   status   char(3)      #   |   |
 |     #   ref_by   int unsigned #>--+   |
 |     #   maillst  varchar(127) #       |
 |     #   link     varchar(255) #       |
 |     #   descr    blob         #       |
 |     ###########################       |
 |                                       | hat
 |  +------------------------------------+ Absätze
 |  |
 |  |  ###########################
 |  |  #        PARAGRAPH        #
 |  |  ###########################
 |  +-># P proj_id  int unsigned #
 |  n  # P vers_id  int unsigned #
 +---->#   memb_id  int unsigned #
    n  # P parastr  varchar(251) #
       #   submit   datetime     #
       #   send_by  varchar(120) #
       #   headln   varchar(255) #
       #   text     blob         #
       ###########################

(3) Ja, ich weiss, keine schöne Grafik, und das Datenmodell könnte an der einen oder anderen Stelle verbessert werden (z.B. memb_id aus PARAGRAPH raus).

(4) Die Projekthierarchie entsteht durch ref_by in METATEXT und die Intersection SUBPJS mit subpj_id (ein t-ärer Baum, so hiess das zumindest in unserem Studium). Damit verweist das untergeordnete Projekt auf das übergeordnete (ref_by) und das übergeordnete Projekt auf alle seine untergeordneten Projekte (subpj_id). Nachfolgend die Skripte zur Erzeugung der Tabellen (par_temp habe ich oben weggelassen, nur eine temporäre Tabelle zu sortieren von Absätzen).

Tabelle MEMBER

(5)

#
# Projects are used by members, or initiated by members = maintainer
#
create table member (
memb_id    smallint unsigned       not null auto_increment,# member id
last_n   varchar(50)             not null,       # last name
first_n  varchar(30)             not null,       # first name
email   varchar(80)             not null,       # email address
passwd       varchar(50)             not null,       # password
subscr    datetime                not null,       # subcription date
primary key( memb_id ),
unique key email_idx( email )
);

Tabelle METATEXT

(6)

# Metatexts generally chracterize projects.
# A member given in the metatext record is the maintainer of the project.
# Valid values of status: "ini" (initialized), "act" (active), "arc" (archives),
# and "fin" (finished).
#
create table metatext (
proj_id  smallint unsigned       not null,       # project id
vers_id smallint unsigned       not null,       # version number
memb_id     smallint unsigned       not null,       # member id
title    varchar(255)            not null,       # project title
titlabb      varchar(15)             not null,       # title abbreviation
submit  datetime                not null,       # submission date
status     char(3)                 not null,       # text status
ref_by smallint unsigned       not null,       # referenced by project
maillst      varchar(127)            not null,       # mailinglist name
link      varchar(255)            null,           # link to base text
descr    blob                    not null,       # short description
primary key( proj_id, vers_id ),
key titlabb_idx( titlabb )
);

Tabelle PROJMEMB

(7)

#
# The intersection projmemb joins between member and metatext: a member can
# join one or more projects, and a project has 1 or more members
#
create table projmemb (
proj_id     smallint unsigned       not null,       # project id
memb_id smallint unsigned       not null,       # member id
primary key( proj_id, memb_id ),
key proj_idx( proj_id ),
key memb_idx( memb_id )
);

Tabelle SUBPJS

(8)

#
# all sub projects of a main project
#
create table subpjs(
proj_id smallint unsigned   not null,
subpj_id smallint unsigned not null,
primary key( proj_id, subpj_id ),
key proj_idx( proj_id ),
key subpj_idx( subpj_id )
);

Tabelle PARAGRAPH

(9)

#
# A paragraph is the basic text block of a text project. Paragraphs are
# described by the paragraph string builded by three parts:
# -> 6 leading chars defining 999999 paragraphs of the text in maximum
# -> 61 x 4 chars defining 61 comment levels with max. 9999 paragraphs each
# -> 1 trailing char indicating the string end ("#")
#
create table paragraph (
proj_id   smallint unsigned       not null,       # projekt id
vers_id smallint unsigned       not null,       # version number
memb_id     smallint unsigned       not null,       # member id
parastr  varchar(251)            not null,       # paragraph string
submit    datetime                not null,       # submission date
send_by    varchar(120)            null,           # subm date and name
headln  varchar(255)            null,           # paragraph headline
text    blob                    not null,       # paragraph text
primary key( proj_id, vers_id, parastr ),
key pj_ver_idx( proj_id, vers_id )
);

Tabelle PAR_TEMP

(10)

#
# paragraph temporary table to copy versions of texts
#
create table par_temp (
proj_id       smallint unsigned       not null,       # projekt id
vers_id smallint unsigned       not null,       # version number
memb_id     smallint unsigned       not null,       # member id
parastr  varchar(251)            not null,       # paragraph string
submit    datetime                not null,       # submission date
send_by varchar(120)               null,           # subm date and name
headln  varchar(255)            null,           # paragraph headline
text    blob                    not null        # paragraph text
);