phpGedView DATABASE TABLE LAYOUT

This needs updating to the later structure used by phpGedView version 4, but I hope to strip some of the tables before I get round to updating the contents here.

PhpGedView uses a very simple database table layout because it operates primarily on the GEDCOM data and only needs the database for search and retrieval. There are only a few tables in the database:

Table	Description
pgv_blocks	Description of each user's Portal page	bitweaver modules
pgv_dates	Stores decoded date information from GEDCOM records
pgv_families	All the families in the GEDCOM
pgv_favorites	Stores users favorites	bitweaver user prefs
pgv_individuals	All the individuals in the GEDCOM
pgv_messages	Messages to and from users	bitweaver messaging
pgv_names	Stores decoded name information from GEDCOM records
pgv_news	Stores news items for the Index and Portal pages	various other packages
pgv_other	All other level 0 GEDCOM records (i.e., repositories, media objects, notes, etc.)
pgv_placelinks	Cross-reference between places and individuals and families
pgv_places	Place hierarchy
pgv_sources	All the sources in the GEDCOM
pgv_users	Table for user data (replaced by users_users)	BitUser

The tables are all very similar. They each have a field for the GEDCOM ID, a field to tell which GEDCOM file the record was imported from, a few fields for things like quick retrieval of name information, and a field for the raw GEDCOM record data.

pgv_individuals

Field	Size	Notes
i_id	VARCHAR(20)	GEDCOM individual ID
i_file	SMALLINT	ID number of the GEDCOM file the record is from
i_rin	VARCHAR(20)	Individual's RIN number
i_isdead	INT	Alive/dead status of individual -1 = not calculated yet 0 = alive 1 = dead
i_sex	CHAR(1)	Alive/dead status of individual
i_gedcom	TEXT	Raw GEDCOM record for this individual

pgv_families

Field	Size	Notes
f_id	VARCHAR(20)	GEDCOM family ID
f_file	SMALLINT	ID number of the GEDCOM file the record is from
f_husb	VARCHAR(20)	ID of the husband
f_wife	VARCHAR(20)	ID of the wife
f_chil	TEXT	List of children IDs, semi-colon (;) delimited
f_gedcom	TEXT	Raw GEDCOM record for this family
f_numchil	INT	Number of children in this family

pgv_sources

Field	Size	Notes
s_id	VARCHAR(20)	GEDCOM source ID
s_file	SMALLINT	ID number of the GEDCOM file the record is from
s_name	VARCHAR(255)	Abbreviated title of the source
s_gedcom	TEXT	Raw GEDCOM record for this source
s_dbid	CHAR(1)	?

pgv_other

Field	Size	Notes
o_id	VARCHAR(20)	GEDCOM record ID
o_file	SMALLINT	ID number of the GEDCOM file the record is from
o_type	VARCHAR(5)	Type of GEDCOM record (REPO, ADDR, NOTE, OBJE, etc)
o_gedcom	TEXT	Raw GEDCOM record for this item

pgv_names

Field	Size	Notes
n_id	VARCHAR(20)	Individual ID that this name corresponds to
n_file SMALLINT	ID number of the GEDCOM file the record is from
n_num	INT	Version number of the name ( allow multiple names )
n_type	VARCHAR(15)	Type of Name
n_full	VARCHAR(255)	Name in GEDCOM format, with / / around the surname
n_list	VARCHAR(255)	First letter of the surname
		The following fields are only used in INDI records
n_surname	VARCHAR(255)	Surname for this name record
n_surn	VARCHAR(255)	Surname used for soundex
n_givn	VARCHAR(255)	Given used for soundex
n_soundex_givn_std	VARCHAR(255)	Standard soundex given name
n_soundex_surn_std	VARCHAR(255)	Standard soundex surname name
n_soundex_givn_dm	VARCHAR(255)	DM soundex given name
n_soundex_surn_dm	VARCHAR(255)	DM soundex surname name

pgv_dates

d_day # The day of month for this date
d_month # The 3 letter abbreviation for month of year
d_mon # Integer 1-12 for the month of year
d_year # The year for this date
d_datestamp # A date stamp of the form YYYYMMDD used for simple
. # comparisons
d_fact # The fact that this date was associated with
d_gid # The gedcom XREF ID where this fact and date were found
d_file # The gedcom file id where this fact was found
d_type # Used if this date uses an alternate calendar type

pgv_blocks

b_id INT(11) # Record ID
b_username VARCHAR(100) # User name whom block belongs to
b_location VARCHAR(30) # Location of the block.
. # Main column or right column
b_order INT(11) # Position of the block within the column
b_name VARCHAR(255) # Name of the block
b_config TEXT # Configuration settings for this block

pgv_favorites

fv_id INT(11) # Record ID
fv_username VARCHAR(30) # User name whom the favorite belongs to
fv_gid VARCHAR(10) # ID of the favorite
fv_type VARCHAR(10) # Type of favorite (currently only INDI)
fv_file VARCHAR(100) # File that this favorite belongs to
fv_url VARCHAR(255) # The URL for this favorite if it is not one of
. # the basic types
fv_title VARCHAR(255) # A title for URL based favorites
fv_note TEXT # Optional descriptive information about this favorite

pgv_messages

m_id INT(11) # Record ID
m_from VARCHAR(255) # Name or email address of the sender
m_to VARCHAR(30) # Destination user name
m_subject VARCHAR(255) # Subject of the message
m_body TEXT # Body text of the message
m_created VARCHAR(255) # Time stamp when the message was created

pgv_news

n_id INT(11) # Unique identifier
n_username VARCHAR(100) # User name or GEDCOM the News item belongs to
n_date INT(11) # Time stamp of last update
n_title VARCHAR(255) # Title of the article
n_text TEXT # Body text of the article

pgv_places

p_id INT(11) # Unique identifier
p_place VARCHAR(150) # Place name
p_level INT(11) # Level of the place in the hierarchy,
. # 0 is the country or state
p_parent_id INT(11) # ID of this item's parent place in the
. # hierarchy. A city's parent would be the
. # county it is in, a county's parent would be
. # a state or province, and a state or province
. # would have a country as parent.
p_file INT # ID number of the GEDCOM file the record is from

pgv_placelinks

pl_p_id INT(11) # Unique identifier
pl_gid VARCHAR(30) # Family or individual ID referencing this place
pl_file INT # ID number of the GEDCOM file the record is from

pgv_users

This has been replaced by BitUser with additional fields stored in the user permission and other generic BW storage.

media

m_id INT NOT NULL
m_media VARCHAR(15)
m_ext VARCHAR(6)
m_titl VARCHAR(255)
m_file VARCHAR(255)
m_gedfile BIGINT
m_gedrec BLOB
PRIMARY KEY (m_id)

media_mapping

mm_id INT NOT NULL
mm_media VARCHAR(15) NOT NULL default ''
mm_gid VARCHAR(15) NOT NULL default ''
mm_order INT NOT NULL default '0'
mm_gedfile INT default NULL
mm_gedrec BLOB
PRIMARY KEY (mm_id)

PhpgedviewPackage