Database Overview

This section is still under development, and is mostly being used to help me redesign variant title and pseudonym support in the ISFDB.

Titles

Editing

There are three ways to select titles for editing or merging:

1. When displaying a particular title (using title.cgi), the Editing Tool labeled Title Data can be used to directly edit the title.
2. When in the author's bibliography, the Editing Tool labeled Titles can be used to view a table of the author's titles. Check boxes exist to select titles for merging, or individual titles can be viewed, allowing editing via method #1 above.
3. The title table from method #2 only allows viewing 100 titles at a time, and it may be desirable to merge titles on two different tables. In that case, the Advanced Search option can be used to select the appropriate titles, which will be displayed in table mode.

Variant Titles

In ISFDB1 and in the initial version of ISFDB2, variant titles were simply embedded by convention into the title string. The caret character was used to delimit the titles as follows:

Canonical Title^Variant Title1^Variant Title 2

And the online ISFDB software would display it as:

Canonical Title [vt Variant Title1] [vt Variant Title 2]

This approach has two serious limitations: it can't link variant titles to variant authors (a case discussed later), and it doesn't allow a publication (such as a magazine) to list the title as it was actually published in that particular issue.

The new approach is to add the variant titles into the titles table. The field title_parent is used to discriminate between canonical titles and variant titles. When title_parent is zero, the title is a canonical title. When title_parent is non-zero it is a variant title, and the field points to the parent canonical title, as seen in the diagram below:

                 +-----------------+
+----------------| Canonical Title |<-----------+
|                +-----------------+            |
|                                               |
|  +-------+              +-----------------+   |
|  | Pub 1 |<-------------| Title Variant 1 |---+
|  +-------+              +-----------------+   |
|                                               |
|  +-------+              +-----------------+   |
|  | Pub 2 |<-------------| Title Variant 2 |---+
|  +-------+              +-----------------+   |
|                                               |
|  +-------+              +-----------------+   |
|  | Pub 3 |<-------------| Title Variant 3 |---+
|  +-------+              +-----------------+
|
|  +-------+
+->| Pub 4 |
|  +-------+
|
|  +-------+
+->| Pub 5 |
   +-------+

The ISFDB software then outputs the following:

Canonical Title (1973)
  o Variant Titles:
  o Title Variant 1 (1973)
  o Title Variant 2 (1982)
  o TItle Variant 3 (1995)

Given an author_id=X, the author's canonical titles can be found with:

select titles.* from titles,canonical_author where
  canonical_author.author_id=X and
  canonical_author.title_id=titles.title_id and
  titles.title_parent=0;

While the author's variant titles can be found with:

select titles.* from titles,canonical_author where
  canonical_author.author_id=X and
  canonical_author.title_id=titles.title_parent;

Authors

Pseudonyms

                 +-----------------+
      +----------| Canonical Title |<-----------------+
      |          +-----------------+                  |
      V                                               |
+------------------+              +-----------------+ |
| Canonical Author |<-------------| Title Variant 1 |-+
+------------------+              +-----------------+ |
                                                      |
+-------------+                   +-----------------+ |
| Pseudonym   |<------------------| Title Variant 2 |-+
+-------------+                   +-----------------+