# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Jun 02, 2003 at 01:58 PM
# Server version: 3.23.56
# PHP Version: 4.2.3
# Database : `pipian_mangadb`
# --------------------------------------------------------

#
# Table structure for table `person`
#

# Current (2003-06-02) Schema for Open Manga DB, with notation.
#
# All romanization should be done using the Hepburn romanization, specifically
# in wapuro style (long vowels are written in the Roman alphabet like they are
# in Hiragana.  i.e. long o is typically "ou", not "oo" and long e typically is
# "ei", not "ee") but with all english loan words spelled as in English (e.g
# Meedo/Meido -> Maid)
#
# All dates are yyyy-mm-dd
#
# All Japanese characters should be encoded in UTF-16, with any
# characters/punctuation in the Roman alphabet rendered in full-width
# characters as long as they are rendered within Japanese language fields
# (excepting romaji fields)

CREATE TABLE person (
  aid int(11) NOT NULL auto_increment,
  name_jp text NOT NULL,

# name_jp should be the Japanese name of the person in traditional Japanese
# last name first, first name last order, utilizing the original kanji.
#
# If the person is an American, do not fill this spot (or should we use
# full-width alphabet?)
#
# If it's the responsibility of a group (e.g. CLAMP), presumably, we fill in
# the group name here instead of the mangaka. Alternately we may need to create
# a separate group table, and add a field for the series table specifying
# whether the manga is done by a group or an individual.  The group table would
# link to individual group members through a comma-delimited list.

  name_jp_fu text NOT NULL,

# name_jp_fu should be the same as name_jp, utilizing the furigana in place of
# the kanji.  It should be left blank if an American

  name_en varchar(255) NOT NULL default '',

# name_en should be the American name, "surname, first name" or if Japanese,
# the romanization of the furigana, but containing the comma.

  name_en_alt longtext NOT NULL,

# Just in case there's a nickname, use name_en_alt.  Otherwise leave it blank.

  birth date NOT NULL default '0000-00-00',
  death date NOT NULL default '0000-00-00',

# Other ideas:
#
# birthplace: Location of birth, formatted "(city),
#             (state/province/prefecture), (ISO-3166-A3 (3-letter) code for
#             birth nation.)" All of these are optional, so not all three are
#             needed. It is preferred, however, that verification can be
#             provided of the birthplace (so that assumptions are not made).

  PRIMARY KEY  (aid)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `publisher`
#

CREATE TABLE publisher (
  pid int(11) NOT NULL auto_increment,
  name_jp text NOT NULL,

# For these names, and all others with _jp, _jp_fu, _jp_ro, _en, and possibly
# _jp_alt and _en_alt:
#
# _jp: Uses original name including Kanji.
# _jp_fu: Uses furigana.
# _jp_ro: Uses romaji.
# _en: Is the standardized English name (as used in commercial ventures if
#      applicable.  If not, then the general form seen on
#      websites/scanlations. This may be the same as the romaji version.)
#
# (Not always present)
# _jp_alt: Alternate Japanese forms (e.g. Pokemon for Pocket Monsters) written
#          first in their kanji/kana style,separated next with a "|" before
#          writing in romaji (e.g. Pokemon for Pocket Monsters).  Multiple
#          alternate names should be separated using commas.
# _en_alt: Alternate English forms (e.g. KareKano for Kareshi Kanojo no Jijou),
#          separated using semi-colons.  May be the same as alternate Japanese
#          forms and includes unofficial scanlation/translation titles.

  name_jp_fu text NOT NULL,
  name_jp_ro varchar(255) NOT NULL default '',
  name_en varchar(255) NOT NULL default '',
  country char(3) NOT NULL default '',

# country should be entered according to ISO-3166-A3 (3-letter) and should be
# the country of the publisher.

  isbnid varchar(10) NOT NULL default '',

# isbnid should be the ISBN prefix utilizing dashes and the first two numbers
# (e.g. "4-04" for Kadokawa)

  mags_ro longtext NOT NULL,

# mags_ro should be a comma-delimited list of the comic magazines the publisher
# publishes using romaji
#
# Other ideas:
#
# imprints_ro: a comma-delimited list of the comic imprints the publisher
# publishes using romaji (e.g. "Kadokawa Comics A", etc for Kadokawa)

  PRIMARY KEY  (pid)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `series`
#

CREATE TABLE series (
  sid int(11) NOT NULL auto_increment,
  name_jp text NOT NULL,
  name_jp_fu longtext NOT NULL,
  name_jp_ro longtext NOT NULL,
  name_jp_alt longtext NOT NULL,
  name_en varchar(255) NOT NULL default '',
  name_en_alt longtext NOT NULL,
  mkid int(11) NOT NULL default '0',

# The following ids are preferred to be listed in the order they are listed on
# the Japanese release (with the exception of translator, which should be
# ordered according to the American release, naturally).
#
# mkid: The mangaka (illustrator)
# aid: The original author (if given and not the same as the mangaka)
# tid: The translator
# otherid: Any other position.
#
# mkid_other, aid_other, tid_other, and otherid are comma-delimited lists of
# the additional mangaka/authors/translators/additional people.

  mkid_other varchar(255) NOT NULL default '',
  aid int(11) NOT NULL default '0',
  aid_other varchar(255) NOT NULL default '',
  tid int(11) NOT NULL default '0',
  tid_other varchar(255) NOT NULL default '',
  otherid varchar(255) NOT NULL default '',
  otherid_job longtext NOT NULL,
  mag_jp_en varchar(255) NOT NULL default '',

# mag_jp_en should probably be renamed mag_jp_ro to match other _jp_ro columns.
# In any case, mag_jp_ro contains the name of the comic magazine in which the
# manga first appeared in Japan.

  begindate_jp date NOT NULL default '0000-00-00',

# begindate_jp and enddate_jp mark the dates of the first and last issues of
# the comic magazine that contained the manga.  Presumably there would be the
# same for American comics as well (including an mag_us field)

  enddate_jp date NOT NULL default '0000-00-00',
  pid_jp int(11) NOT NULL default '0',

# pid_jp, pid_us, and pid_other contain the publisher ids of the publishers in
# Japan, the US, and other places. pid_other is comma-delimited.

  pid_us int(11) NOT NULL default '0',
  pid_other varchar(255) NOT NULL default '',
  scanid varchar(255) NOT NULL default '',

# scanid contains the id for scanlation groups that are actively working on, or
# have previously worked on this manga (perhaps co-oping with Daily Manga?)
#
# Future fields:
#
# Probably none with reviews/plot summaries (shunt those to another table).
# Perhaps the average rating?

  PRIMARY KEY  (sid)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `volume`
#

CREATE TABLE volume (
  vid int(11) NOT NULL auto_increment,
  sid int(11) NOT NULL default '0',

# sid is the series id to which the manga volume belongs.

  volnum int(11) NOT NULL default '0',

# volnum is the volume number of the manga volume.  (It should be 0 if it is a
# one-volume series?)

  isbn varchar(10) NOT NULL default '',

# isbn should contain the ISBN of the volume without dashes or spaces. (Should
# this be an int?)

  country char(3) NOT NULL default '',

# country is the ISO-3166-A3 code for the country for which the volume was
# intended (not published)

  price_yen int(11) NOT NULL default '0',

# price_yen should contain the price in yen (without tax).  (Should this be
# expanded to the MSRP for all manga?)

  ccode varchar(4) NOT NULL default '',

# ccode contains the C-code for the published manga (only applicable for
# Japanese manga) (typically "0979")

  title_jp text NOT NULL,

# The title fields work exactly like the name fields listed above.

  title_jp_fu longtext NOT NULL,
  title_jp_ro varchar(255) NOT NULL default '',
  title_en varchar(255) NOT NULL default '',
  title_en_alt longtext NOT NULL,
  firstchnum int(11) NOT NULL default '0',

# firstchnum and lastchnum are the first and last chapters the volume contains.
# (NOT the number of chapters in the volume)

  lastchnum int(11) NOT NULL default '0',
  chnames_jp longtext NOT NULL,

# The chnames fields work similar to the name/title fields listed above, save
# for the fact that they are semi-colon-delimited (or in the case of chnames_jp
# and chnames_jp_fu, comma-delimited) lists of the chapter names.
#
# chnames_en_alt should contain alternate translations of the chapter names
# semi-colon-delimited by chapter (including those without alternate names),
# and delimited by "|" within chapters (if it has multiple alternate names)

  chnames_jp_fu longtext NOT NULL,
  chnames_jp_ro longtext NOT NULL,
  chnames_en longtext NOT NULL,
  chnames_en_alt longtext NOT NULL,
  firstpub date NOT NULL default '0000-00-00',

# firstpub contains the date of the first publication (if available)

  otherpubs longtext NOT NULL,

# otherpubs contains a comma-delimited list of subsequent publications, in the
# form "(publication number)=(date of publication)"
#
# Future fields:
#
# Some tied with competitive pricing perhaps.
# Possibly links to cover scans?
# Very likely a "version" field (since there are comics like Ranma that are now
# being re-released, and others with both tankouban and wideban versions.)

  PRIMARY KEY  (vid)
) TYPE=MyISAM;