[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