Database Doc

Web Pages

There are several reasons for wanting to track web pages in the database:

• Common encapsulation of URL, link text and description, which can be used consistently in various places (section listings, newest pages, site map, cross-references).
• Ability to store meta tag information (desc, keyw, etc.) external to static web pages.
• Page-to-page cross-references, which become a simple table of web page identifier pairs.

The web page table needs to track both local and external web pages, since cross-references should work equally well.

Table: url
id	int unsigned auto_increment primary key: Unique identifier for linking to this table.
url	varchar(255) not null: URL, can be local or external. Local URLs do not specify "http:" or hostname.
title	varchar(127) null: Title text for constructing links to URL. If null, use url.
descr	text: Web page description, typically used after title in a cross-reference list. Can be used for meta description for local web pages.
keyw	text: Meta keyword list for local pages. (Sort of a half-assed approach, since keyword search would be better implemented through separate tables.
meta	text: Supplemental meta tag list for local pages.
mod_date	date null: Explicit modification date for local pages. Used for generating newest page list, general date sorting. Not necessarily updated for minor edits like fixing typos. Should be set for all local pages, null for all external pages.
Table: plink
page	int unsigned not null: Link to url for local web page.
link	int unsigned not null: Link to url for reference web page (local or external).
Table: alink
artist_id	int unsigned not null references artist.id: Link to artist identifier.
url_id	int unsigned not null references url.id: Link to url identifier.

What else?

• A creation date as well as a modification date?
• Page sequence information (perhaps best stored externally).
• Fields for validating external URLs (date checked, last good date).
• Backlink to article (could be used for generating tag lines; if 1:1, article could be found in second query based on url id).
• Something to order a set of URLs (sort field in place of title, or something else?).

Bibliography

Table: author
id	int unsigned auto_increment primary key:
name	varchar(127) not null: Author name.
sort	varchar(127) not null: Sort key for author name.
entry	text null:
Table: publication
id	int unsigned auto_increment primary key:
name	varchar(127) not null: Publication name.
subt	varchar(127) null: Publication subtitle.
sort	varchar(127) not null: Sort key for publication name.
entry	text null:
sbn	varchar(31) null: Standard book number (non-books null).
Table: article
id	int unsigned auto_increment primary key:
author_id	int unsigned not null: Link to author table.
title	varchar(255):
sort	varchar(120):
pub_date	date null:
pub_id	int unsigned null: Link to publication table.
loc	varchar(60):
url	int unsigned not null: Link to url table.

Artist/Album Lists

Table: rlist
list	char(4) not null: List identifier.
artist_id	int unsigned not null references artist.id: Artist identifier.
Table: alist
list	char(4) not null: List identifier.
album_id	int unsigned not null references album.id: Album identifier.
Table: ulist
list	char(4) not null: List identifie.
url_id	int unsigned not null references url.id: URL identifier.
Table: listlist
list	char(4) not null: List identifier.
listgroup	char(4) not null: List group identifier: rg = Region.
title	varchar(127) not null: List name.
sort	varchar(127) not null: Sort key.
descr	varchar(255) null: Table description.

These tables are used to create arbitrary subset lists of artists and/or albums. Some example uses of these lists:

1. Generic Sets. Initially we defined two sets, each using rlist for associated artists, alist for compilations, and ulist for essays: Africa and Reggae.
2. The lists of Consumer Guide entries that appeared during a decade but were not included in the corresponding Consumer Guide book. For example, Christgau's Consumer Guide: Albums of the '90s.