[B-Greek] Scientific Greek Interlinear Project database definition
Alan Bunning
bunning at myrealbox.com
Thu Feb 5 14:21:50 EST 2004
This design is currently being picked over by some of my peers. Other fields
and tables will certainly have to be added at some point. There is a rhyme
and reason to why it is set up this way.
CREATE TABLE roots (
rootid smallint(5) unsigned, # Strong's number with additional numbers
root varchar(40) NOT NULL, # root Greek word
transliterate varchar(40), # transliterated Greek word (Strong's)
phonetic varchar(50), # phonetic pronunciation (Strong's)
alternates varchar(200), # alternate words
multi char(1), # M - multiple words make up a phrase (to be moved)
proper char(1), # Pp - proper noun, Ll - location, U - uppercase, C -
capitalized
parsing varchar(30), # the bit of parsing known by the root
kjv_words text, # various KJV translations of the word (can be deleted)
kjv_total smallint(5) unsigned, # total number of times used in KJV (can
be deleted)
tdnt varchar(30), # Theological Dictionary of the New Testament
references
links varchar(80), # links to other related Strong's numbers
literal varchar(80), # literal meaning (Strong's)
origin varchar(255), # origin of word (Strong's)
thayer text, # Thayer's definition
strong text, # Strong's definition
PRIMARY KEY (rootid),
UNIQUE (root),
INDEX (multi),
INDEX (proper));
CREATE TABLE words (
word varchar(20) NOT NULL, # Greek word
parsing varchar(20) NOT NULL, # parsing of Greek word
phonetic varchar(30), # phonetic pronunciation of Greek word
rootid varchar(20) NOT NULL, # root Greek word
translate varchar(40), # English translation
PRIMARY KEY (word,parsing),
UNIQUE (translate),
FOREIGN KEY (rootid) REFERENCES roots(rootid));
CREATE TABLE phrases (
phraseid smallint(5) unsigned, # Strong's number with additional numbers
phrase varchar(255), # Greek phrase
translate varchar(255), # English translation
PRIMARY KEY (phraseid));
#CREATE TABLE equivalence (
#have not decided on how to handle differences in spelling yet
CREATE TABLE texts ( # there will be one such table for each text
verse smallint(6) unsigned NOT NULL, # bbccvv - book, chapter, and verse
position smallint(2) unsigned NOT NULL, # position of word in verse
word varchar(20), # Greek word
parsing varchar(20), # parsing of Greek word in that location
PRIMARY KEY (verse,position),
FOREIGN KEY (word,parsing) REFERENCES words(word,parsing));
CREATE TABLE sources (
source varchar(10), # id of text that will correspond to a table name
description varchar(255), # description of text
date date, # date of text's origin
type char(1), # C - composite, O - original
PRIMARY KEY (source),
INDEX (date),
INDEX (type));
#For phase 2 there will also have to be a separate table containing the
variant links and apparatus
More information about the B-Greek
mailing list