Document Repository Administrators' Guide:
Chapter 3
Database structure

This chapter assumes a working knowledge of SQL and Oracle databases in general. It provides the underlying structure of the database tables used in this project. It does not explain, nor does it intend to explain, why certain design decisions were made nor why certain features were used. Refer to any good Oracle primer for additional information.

The repository repo schema consists of the following primary tables:

	documents--The main table
	states--Where the public/blessed/locked/frozen bits are defined
	practice--Practices
	class, class_type--Define class/subclass
	ex_ct_auth--Table to store info on ex-CT folks no longer in resources

There are three other tables that may be relevant: long_data is used to store notes for documents. auth_stat helps out with the author sorting and unique author statistics. doc_history is a sample audit trail and currently only used in development.

There are some foreign key constraints defined for the documents table, namely that the state and class/subclass must be defined in the states and class tables. There is no constraint on practice since it can be null.

 

---

documents table

doc_id	number not null (primary key)
doc_ct_auth	varchar2(256)
doc_non_ct_auth	varchar2(256)
doc_sortkey	varchar2(60)
doc_title	varchar2(256) not null
doc_description	varchar2(256)
doc_keywords	varchar2(512)
doc_revision	varchar2(10)
doc_published	date
doc_url	varchar2(256)
doc_st_id	number not null
doc_public	number(1)
doc_pr_id	number
doc_cl_id	number not null
doc_notes	number
doc_lastupdate	date
doc_updater	number

 

---

states table

st_id	number (primary key)
st_desc	varchar2(20)

 

---

practice table

pr_id	number (primary key)
pr_code	char
pr_desc	varchar2(30)

 

---

class table

cl_id	number (primary key)
cl_class	char
cl_subclass	char
cl_subname	varchar2(30)

 

---

class_type table

ct_class	char (primary key)
ct_name	varchar2(30)

 

---

doc_history table

dh_id	number not null (primary key)
dh_doc_id	number not null
dh_st_id	number
dh_type	char
dh_timestamp	date not null

 

---

long_data table

ld_id	number not null (primary key)
ld_data	long

 

---

ex_ct_auth table

eca_login	varchar2(10) not null (primary key)
eca_lname	varchar2(30)
eca_fname	varchar2(30)

 

---

auth_stat table

as_name	varchar2(40) not null (primary key)
as_doc_ct	number not null
as_isct	number

 

---

Synonyms

create synonym members for resources.members;

 

---

Foreign keys

alter table documents
add (foreign key (doc_st_id) references states(st_id) on delete cascade,
     foreign key (doc_cl_id) references class(cl_id) on delete cascade,
     foreign key (doc_notes) references long_data(ld_id) on delete cascade
    );

alter table class
add (foreign key (cl_class) references class_type(ct_class) on delete cascade
    );

 

---

Sequences

create sequence doc_id_seq;
grant select on doc_id_seq to repo_role;

create sequence cl_id_seq;
grant select on cl_id_seq to repo_role;

create sequence ld_id_seq;
grant select on ld_id_seq to repo_role;

create sequence dh_id_seq;
grant select on dh_id_seq to repo_role;

 

---

Grants

grant all on documents to repo_role;
grant all on states to repo_role;
grant all on practice to repo_role;
grant all on class_type to repo_role;
grant all on class to repo_role;
grant all on doc_history to repo_role;
grant all on ex_ct_auth to repo_role;
grant all on auth_stat to repo_role;

 

---

Triggers

CREATE or REPLACE trigger doc_history BEFORE insert or update or delete on repo.documents FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO repo.doc_history ( dh_id, dh_doc_id, dh_st_id, dh_type, dh_timestamp ) VALUES ( dh_id_seq.nextval, :old.doc_id, :old.doc_st_id, 'D', SYSDATE ); END IF; IF UPDATING THEN INSERT INTO repo.doc_history ( dh_id, dh_doc_id, dh_st_id, dh_type, dh_timestamp ) VALUES ( dh_id_seq.nextval, :new.doc_id, :new.doc_st_id, 'U', SYSDATE ); END IF; IF INSERTING THEN INSERT INTO repo.doc_history ( dh_id, dh_doc_id, dh_st_id, dh_type, dh_timestamp ) VALUES ( dh_id_seq.nextval, :new.doc_id, :new.doc_st_id, 'I', SYSDATE ); END IF; END doc_history; /

Back to "Contents"

Back to Chapter 2, "Deleting records"

Back to the repository

Back to CLINK

Submit a bug report or enhancement request