Home Was ist ot ? Regeln Mitglieder Maintainer Impressum FAQ/Hilfe
Datenmodell von open theory
Maintainer: Stefan Meretz, Version 1, 16.05.2000
Projekt-Typ: halboffen
Status: Archiv
(1) ASCII-Grafik (genaue Definitionen unten). Anmerkungen: MySQL kennt keine Foreign-Keys, trotzdem tue ich mal so. Lies "Metatext" als "Project".
########################### 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).
#
# 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 )
);
# 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 )
);
#
# 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 )
);
#
# 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 )
);
#
# 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 )
);
#
# 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
);