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

Hack Database Schema

The idea here is to throw together a very quick/dirty database so we can start making use of the data, instead of having to wait until all of the little quirks and details can be sorted out.

General

We need to distinguish between who does what, ergo we need a list of users.

Table: hk_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.
sort	varchar(128) not null: Sort key.

The basic unit of content is the node, which is written by someone at some date. In the long run we need to be able to associate multiple nodes with each object, but for now we'll just do one node per object.

Table: hk_node
id	int unsigned auto_increment primary key: ID, used by other tables to refer to a node.
muid	int unsigned references hk_user.id: User who wrote node.
mdate	datetime: Date/time node written. This can be in the past for reviews that have been recovered from other sources.
data	text: Node contents.
mtime	timestamp: Modification timestamp, updated whenever the node is changed. This is used for identifying recent changes.

Discography

These tables implement a very simple discographical database. Each album has a title, artist, and label. The artists and labels are broken out into their own tables. Variable artists compilations have unique artist entries, typically with an empty name field, a sort field that matches the album title (this allows compilations to be sorted by title into the same sort set as artists), and type set to 0.

A single node can be attached to each artist, label, album. The album nodes are typically reviews.

Table: hk_artist
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an artist.
name	varchar(255) not null: Artist name.
qual	varchar(32) null: Optional qualifier for name, used to uniquely identify artists with shared names.
sort	varchar(255) not null: Sort key.
type	tinyint: Artist info type. Eventually we want to be able to distinguish levels of artist importance for browse lists, but for now we need to distinguish between Various Artist compilations (0) and real artists 1.
nid	int unsigned references hk_node.id: Node for info about artist.
Table: hk_label
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an label.
name	varchar(255) not null: Label name.
qual	varchar(32) null: Optional qualifier for name, used to uniquely identify labels with shared names.
sort	varchar(255) not null: Sort key.
nid	int unsigned references hk_node.id: Node for info about label.

Ultimately we need to distinguish between virtual albums and actual releases, but they're merged together here.

Table: hk_album
id	int unsigned auto_increment primary key: ID, used by other tables to refer to an album.
artist	int unsigned references hk_artist.id: Artist.
title	varchar(255) not null: Album title.
label	int unsigned references hk_label.id: Label.
type	int not null: Album type: need to enumerate these. They might make sense as a set.
rec_date	varchar(80) null: Album record date info. Need an encoding here that sorts on the last date, and a function that reformats the encoding. E.g.: yyyy.mm.dd;yyyy.mm.dd- where first date is final recording date, second is first recording date. The latter could be extended into a list of discrete dates, e.g.: yyyy.mm.dd,yyyy.mm.dd, [ . . . ]. The interpretation would first transpose around the ';', then read '-' (hyphen) as denoting a range, ',' (comma) as a list. Missing .mm and/or .dd are treated as 0 but suppressed on print.
rel_date	varchar(40) null: Album release date. Multiple release dates are separated by ',' (comma). In general we want to sort on the earliest release date, but label/review refer to the latest date (if not, later dates should be omitted.
nid	int unsigned references hk_node.id: Node for album review.
rate	varchar(40) null: Rating info. This is ugly, but I suggest: TH,MT [ . . . ] (extended similarly for each user, in user table order). Empty means unrated. Letter grades for now, since this is just temporary scaffolding crap.