ISWA 2010 Database Design
Informational Reference 
Version 2.0.0
May 2nd, 2012

Stephen E Slevinski Jr
http://www.signpuddle.net
slevin@signpuddle.net

Database design
===============
TABLE symbolgroup (
  code INTEGER PRIMARY KEY,
  num INTEGER,
  cat_num INTEGER,
  grp_num INTEGER,
  color TEXT
);

TABLE basesymbol (
  code INTEGER PRIMARY KEY,
  sg_code INTEGER,
  num INTEGER,
  bas_num INTEGER,
  var_num INTEGER,
  vars INTEGER,
  fills INTEGER,
  rots INTEGER
);

TABLE symbol (
  code INTEGER PRIMARY KEY,
  fill INTEGER,
  rot INTEGER,
  bs_code INTEGER,
  sg_code INTEGER,
  w INTEGER,
  h INTEGER,
  id TEXT
);

TABLE iswa_name (
  lang TEXT,
  key TEXT,
  name TEXT
);

For font support, an additional table is needed.

TABLE symfont (
  font TEXT,
  name TEXT,
  author TEXT,
  license TEXT
);

Each font has its own table with a code field and a glyph field. 
The glyph field is either a BLOB for binary such as PNG or TEXT for ASCII such as SVG.

There are currently 4 PNG fonts: font_png1, font_png2, font_png3, font_png4.

Example PNG table
-------------------
TABLE font_png1 (
  code INTEGER,
  glyph BLOB
);

There are currently 5 SVG fonts: font_svg1, font_svg2, font_svg3, font_svg4, font_svg5.

Example SVG table
-------------------
TABLE font_svg1 (
  code INTEGER,
  glyph TEXT
);


Other Database Design
=====================
For sign support, two additional tables can be used.

TABLE sign (
  code INTEGER PRIMARY KEY,
  max_x INTEGER,
  max_y INTEGER
);

TABLE signsymbol (
  sgn_code INTEGER,
  sym_code INTEGER,
  top INTEGER,
  left INTEGER
);

Symbol shape and description.  It is possible to meaningfully describe each symbol.  Below are 2 possible constructs to describe hands and arrows.
For hand shape description, three tables can be used.

Root palm shape (
  code INTEGER PRIMARY KEY,
  name TEXT
);

Root finger shape (
  code INTEGER PRIMARY KEY,
  name TEXT
);

Constructed Hand (
  bs_code INTEGER PRIMARY KEY, 
  rp_code INTEGER,
  fr_code_a INTEGER, # little finger, pinky
  fr_code_b INTEGER, # ring finger
  fr_code_c INTEGER, # middle finger
  fr_code_d INTEGER, # index, pointer finger
  fr_code_e INTEGER, # thumb
);

Arrows can be described with a single table

Arrow Description (
  sym_code INTEGER PRIMARY KEY,
  in_x INTEGER,  # point of entry
  in_y INTEGER,  # point of entry
  out_x INTEGER,  # point of exit
  out_y INTEGER,  # point of exit
);


Symbol interlinking.  It is possible to describe each symbol as less than a symbol, a whole symbol, or a collection of symbols.  A whole symbol is difficult to define and will be a judgement call for each project.  When considering something that is less than a symbol, it could include finger pieces, arrow heads, and other lines that are less meaningful than a whole symbol.   Some symbols can be described as a spatial collection of other symbols.  One difficult symbol to define is the double touch.  From a font perspective, the double touch is made up of 2 single touch symbols.  From a language perspective, a single touch is very different than a double touch.
Base symbols can be linked with a single table

BaseSymbol Link (
  code INTEGER,  #symbols from this base symbol, which may repeat
  bs_code INTEGER,  # are made with symbols from this base symbol(multiple rows)
);

Constructed symbols can be defined with positioning

Symbol Position (
  code INTEGER, #symbol being described, which may repeat
  sym_code INTEGER,  # is made of this symbol at this position (multiple rows)
  top INTEGER,
  left INTEGER
);

Other meta data and hooks.  There are other formal definitions and categorizations of sign language and it's building blocks.  These other definitions are for others to define and explore.  The main tables of SymbolGroups, BaseSymbols, and Symbols can be connected with their respective primary keys.