"structure.html", title=>"Structural Design"); include("inc/dlp-meta.php"); ?>

Notes

This is an in-progress, and incomplete, draft (meaning more details and changes are forthcoming) of a major overhaul/revision of the DLP data model.
This is untested and would require a major overhaul of the current DLP code.

I may sometimes slip into object-model terminology here. The term "class" refers to a table (or, in one special case - resources - a group of tables). "Object" refers to a table row.

See Also

Johannes Simon pointed out: "The Protégé Ontology Editor and Knowledge Acquisition System" as a possible model for the abstraction of resource types. (There's a Protégé Demo - Java 1.3, or higher, required).

Within that website is the document "Ontology Development 101: A Guide to Creating Your First Ontology" (also called "What is an ontology and why we need it").

"The BibTeX format does not only provide sets of fields for a number of common literature types (monographs, articles, proceedings,...), but also allows for extension. A piece of software with which you can not only edit BibTeX format, but also other formats, is pybliographic." –Johannes Simon

Data Model

There are two core tables/table-groups in this model: Resource and User. All tables are either part of a relationship between those two, or are data for one of those two.

Action List Review Interest Resource User Root data model
Figure 1: The six main groups of tables in the model. [legend]

Resource

A 'resource' is a generic container object for identifying any 'loanable' resource (e.g., book, video, cd, tool, etc.) that is available through the 'library'.

There are two tables at the core of a resource: Resource and ResourceCommon. ResourceCommon is data that is shared across one-or-more resources that are effectively the same (e.g., one ResourceCommon record for multiple copies - resources - of the same book).

Resource_link Resource_source Source Resource_picture Picture Action Review Resource detailed model User List Abstract sub-model for resources
Figure 2.1: The role of resources in the data model. [legend]
Resource_link Resourcecommon_picture Resource_picture Resource_source Source Picture Resourcecommon Resource Detailed sub-model for resources
Figure 2.2: The core group of tables for a resource and some related tables. [legend]

resource

CREATE TABLE IF NOT EXISTS resource (
	id mediumint UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT PRIMARY KEY,
	globalid char(255) NOT NULL,
	commonid char(255) NOT NULL,
	owner mediumint UNSIGNED NOT NULL,
	date_added datetime NOT NULL,
	
	resourcetype enum('book','periodical','video','cd','game','other')
		NOT NULL DEFAULT 'book',
	subtype enum('','vhs','dvd','laserdisc','vcd','cd')
		NOT NULL DEFAULT '',
	
	status enum('in','out','lost','other') NOT NULL DEFAULT 'in',
	date_due date,
	
	notes char(255) NOT NULL,
	
	INDEX (globalid),
	INDEX (commonid),
	INDEX (owner),
	INDEX (date_added),
	INDEX (resourcetype,subtype)
)
	COMMENT = "A resource is an item available through the system. E.g., a book.";

An individual resource record has 3 identifiers. The first is the locally unique id number. The second is the globally unique globalid string. The third is the optional, shared, commonid which is used to identify resources that are the multiple of the same thing (e.g., multiple copies of the same book - each book is a unique resource, but they share a common identifier).

Each resource record is related to a user record via the owner field.

The type of resource is specified by the resourcetype (and the optional subtype).

A resource record may be related to a source record via the source field. All relevant source information is kept in the source table (e.g., author name).

Additional metadata fields define information that may or may not be used, depending on the resourcetype.

resourcecommon

CREATE TABLE IF NOT EXISTS resourcecommon (
	commonid char(255) NOT NULL,
	source mediumint UNSIGNED DEFAULT '0' NOT NULL,
	
	title char(255) NOT NULL,
	# link fields moved to resource_link
	description text NOT NULL,
	
	INDEX (commonid),
	INDEX (isbn),
	INDEX (iblist)

)
	COMMENT = "Data that may be shared by multiple resources (e.g., multiple copies of the same book).";

This may be used to link resources to external references (such as a specific web address, or more information about the resource on another website using external IDs such as an isbn number or the upc).

resource_source

CREATE TABLE IF NOT EXISTS resource_source (
	commonid char(255) NOT NULL,
	source mediumint UNSIGNED NOT NULL,
	
	PRIMARY KEY (commonid, source),
	INDEX (source)
)
	COMMENT = "Relate a resourcecommon to a source.";

source

CREATE TABLE IF NOT EXISTS source (
	id mediumint UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT PRIMARY KEY,
	authorlast char(30) NOT NULL,
	authorfirst char(30) NOT NULL,
	email char(255) NOT NULL,
	url char(255) NOT NULL,
	
	INDEX (authorlast, authorfirst)
)
	COMMENT = "Info about a source, which may be linked to multiple resourcecommons.";

resource_picture

CREATE TABLE IF NOT EXISTS resource_picture (
	resource mediumint UNSIGNED NOT NULL DEFAULT '0',
	picture mediumint UNSIGNED NOT NULL,
	
	PRIMARY KEY (resource, picture),
	INDEX (picture)
)
	COMMENT = "Relates a picture to a resource";

resourcecommon_picture

CREATE TABLE IF NOT EXISTS resourcecommon_picture (
	commonid char(255) NOT NULL,
	picture mediumint UNSIGNED NOT NULL,
	
	PRIMARY KEY (commonid, picture),
	INDEX (picture)
)
	COMMENT = "Relates a picture to a resourcecommon";

picture

CREATE TABLE IF NOT EXISTS picture (
	id mediumint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	url tinytext NOT NULL,
	width smallint UNSIGNED NOT NULL,
	height smallint UNSIGNED NOT NULL,
	alttext char(255) NOT NULL,
	title char(255) NOT NULL,
	
	INDEX (resource)
)
	COMMENT = "Define a picture (as an html img element)";

User

Action User_interest List Review Interest Resource User Sub-model for users
Figure 3: Sub-model of Users. [legend]

user

CREATE TABLE IF NOT EXISTS user (
	id mediumint UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY,
	pending tinyint UNSIGNED(1),
	verify char(50),
	username char(15) NOT NULL UNIQUE,
	password char(20) NOT NULL,
	sessionid char(50),
	
	email char(255) NOT NULL,
	email_auth enum('n','y') NOT NULL DEFAULT 'y',
	confirmation_code char(32) NOT NULL,
	confirmation_time datetime,
	
	first_name char(20) NOT NULL,
	last_name char(20) NOT NULL,
	
	phone char(63) NOT NULL,
	phone2 char(63) NOT NULL,
	fax char(63) NOT NULL,
	
	apartment char(5),
	address char(255) NOT NULL,
	address2 char(255) NOT NULL,
	region char(3) NOT NULL,
	country char(2) NOT NULL,
	postal char(7) NOT NULL,
	lon decimal(8,4),
	lat decimal(8,4),
	
	url char(255) NOT NULL,
	aim char(25) NOT NULL,
	icq char(10) NOT NULL,
	msn char(25) NOT NULL,
	
	bio text NOT NULL,
	
	created datetime NOT NULL,
	lastlogin datetime NOT NULL
)
	COMMENT = "A user's access record.";

user_interest

CREATE TABLE IF NOT EXISTS user_interest (
	user mediumint UNSIGNED NOT NULL,
	interest mediumint UNSIGNED NOT NULL,
	
	PRIMARY KEY (user, interest),
	INDEX (interest)
)
	COMMENT = "Links users to multiple interests.";

userreview

CREATE TABLE IF NOT EXISTS userreview (
	user mediumint UNSIGNED NOT NULL,
	source mediumint UNSIGNED NOT NULL,
	positive tinyint UNSIGNED(1) NOT NULL,
	notes char(255) NOT NULL,
	date date NOT NULL,
	
	PRIMARY KEY (user, source),
	INDEX (source)
)
	COMMENT = "Commentary on a user by another user.";

Action

action

CREATE TABLE IF NOT EXISTS action (
	id mediumint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	user mediumint UNSIGNED NOT NULL,
	resource mediumint UNSIGNED NOT NULL,
	status enum('out','request','hold','record') NOT NULL DEFAULT 'out',
	date_action datetime NOT NULL,
	date_due date,
	
	INDEX (user, resource),
	INDEX (resource)
)
	COMMENT = "A transaction request, action, or record.";

List

User Interest Resource List_interest List_resourcecommon List Sub-model for lists
Figure 4: Sub-model of Lists. [legend]

list

CREATE TABLE IF NOT EXISTS list (
	id mediumint UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	owner mediumint UNSIGNED NOT NULL,
	
	title char(255) NOT NULL,
	description text NOT NULL,
	
	INDEX (owner)
)
	COMMENT = "";

list_resourcecommon

CREATE TABLE IF NOT EXISTS list_resourcecommon (
	list mediumint UNSIGNED NOT NULL,
	commonid char(255) NOT NULL,
	sequence int UNSIGNED NOT NULL,
	comment char(255) NOT NULL,
	
	PRIMARY KEY (list, commonid),
	INDEX (commonid)
)
	COMMENT = "Includes a resourcecommon on a list.";

list_interest

CREATE TABLE IF NOT EXISTS list_interest (
	list mediumint UNSIGNED NOT NULL,
	interest mediumint UNSIGNED NOT NULL,
	
	PRIMARY KEY (list, interest)
	INDEX (interest)
)
	COMMENT = "Relates a list to an interest.";

Interest

User_interest List Interest User Sub-model for interests
Figure 5: Sub-model of Interests. [legend]

interest

CREATE TABLE IF NOT EXISTS interest (
	id mediumint UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY,
	interest char(255) NOT NULL UNIQUE,
	
	description char(255) NOT NULL,
	
	count mediumint UNSIGNED DEFAULT '0' NOT NULL
)
	COMMENT = "";

Review

review

CREATE TABLE IF NOT EXISTS review (
	commonid char(255) NOT NULL,
	author mediumint UNSIGNED NOT NULL,
	first_name char(20) NOT NULL,
	last_name char(20) NOT NULL,
	
	review text NOT NULL,
	
	INDEX (commonid),
	INDEX (author)
)
	COMMENT = "";