Home
Guide
  Artists
Links
Archives
  T. Hull
  M. Tatum
Project

Database Schema

Users

The person table is used for various purposes, possibly including maintaining a "who's who."

Table: tz_person
id	int unsigned auto_increment primary key: ID, used by other tables to refer to a person.
name	varchar(128) not null: Person name.
sort	varchar(128) not null: Sort key for person name.
bday	date null: Birth date.
dday	date null: Death date.

A user is someone who has known roles with regard to the website -- usually the right to update content on the website. (We don't plan this website to try to track mere readers.)

Table: tz_user
id	int unsigned auto_increment primary key: ID, used by other tables to refer to a user.
name	varchar(128) not null: User name.
passw	varchar(32) not null: Encrypted user password.
email	varchar(128) not null: User email address.
pid	integer unsigned references tz_person.id null: Person information.

Content

The main unit of content is the node.

Table: tz_node
id	int unsigned auto_increment primary key: ID, used by other tables to refer to a node.
type	integer: Node type. (Could be enumerated, or an index into a node type table.)
cuid	integer references tz_user.id: User ID who originally created (owns) node.
muid	integer references tz_user.id: User ID who last modified node.
ctime	timestamp: Timestamp when node originally created.
mtime	timestamp: Timestamp when node last modified.
data	text: Text/data stored in node.

Static web pages are stored on the file system in directories.

Table: tz_dir
id	int unsigned auto_increment primary key: ID, used by external tables to refer to a directory.
path	varchar(255): Path from website root to directory.

Static web pages are managed by the page table.

Table: tz_page
id	int unsigned auto_increment primary key: ID, used by external tables to refer to a page.
path	varchar(128) not null: Path from website root to page.
dir	integer unsigned references tz_dir.id: Directory page resides in.
cuid	integer references tz_user.id: User ID who originally created (owns) page.
muid	integer references tz_user.id: User ID who last modified page.
ctime	timestamp: Timestamp when page originally created.
mtime	timestamp: Timestamp when page last modified.
title	varchar(255) not null: Page title.

Discography

Artists

Table: tz_artist
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an artist.
name	varchar(250) not null: Artist name.
qual	varchar(32) null: Optional qualifier for name, used to uniquely identify artists with shared names.
sort	varchar(250) not null: Sort key for artist name.
rank	smallint: A ranking number assigned to each artist.
Table: tz_artist_map
major	integer unsigned references tz_artist.id: Primary artist ID.
minor	integer unsigned references tz_artist.id: Secondary artist ID.
Table: tz_artist_node
aid	integer unsigned references tz_artist.id: Artist.
nid	integer unsigned references tz_node.id: Node.
sort	integer: Sort order for sequencing nodes.
Table: tz_group_map
pid	integer unsigned references tz_person.id: Person.
aid	integer unsigned references tz_artist.id: Group.
dates	varchar(255): Some form of encoding for the dates during which the association was active.

Labels

Table: tz_label
id	int unsigned auto_increment primary key: ID, used by other tables to refer to a label.
name	varchar(250) not null: Label name.
qual	varchar(128) null: Optional qualifier for name, used to uniquely identify labels with shared names.
sort	varchar(250) not null: Sort key for label name.
Table: tz_label_map
major	integer references tz_label.id: Primary label ID.
minor	integer references tz_label.id: Secondary label ID.
Table: tz_label_node
aid	integer unsigned references tz_label.id: Label.
nid	integer unsigned references tz_node.id: Node.
sort	integer: Sort order for sequencing nodes.

Album Information

Table: tz_album
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an artist.
aid	int unsigned references tz_artist.id: Artist album is attributed to.
name	varchar(250) not null: Album name (title).
Table: tz_release
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an album release.
album	int unsigned references tz_album.id: Album common information.
label	int unsigned references tz_label.id: Label.
label_no	varchar(80): Label identification number.
rel_date	date: Release date.