Comparing versions
Version 2Current version

phpGedView DATABASE TABLE LAYOUT


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:

TableDescription
pgv_blocksDescription of each user's Portal pagebitweaver modules
pgv_datesStores decoded date information from GEDCOM records
pgv_familiesAll the families in the GEDCOM
pgv_favoritesStores users favoritesbitweaver user prefs
pgv_individualsAll the individuals in the GEDCOM
pgv_messagesMessages to and from usersbitweaver messaging
pgv_namesStores decoded name information from GEDCOM records
pgv_newsStores news items for the Index and Portal pagesvarious other packages
pgv_otherAll other level 0 GEDCOM records (i.e., repositories, media objects, notes, etc.)
pgv_placelinksCross-reference between places and individuals and families
pgv_placesPlace hierarchy
pgv_sourcesAll the sources in the GEDCOM
pgv_usersTable for user data (only exists if using default mysql authentication module)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

i_id VARCHAR(255) # GEDCOM individual ID
i_file INT # ID number of the GEDCOM file the record is from
i_rin VARCHAR(30) # Individual's RIN number
i_name VARCHAR(255) # Person's primary name taken from the first
  1. 1 NAME line stored in GEDCOM name format
i_isdead int(1) # Alive/dead status of individual
  1. -1 = not calculated yet 0 = alive 1 = dead
i_GEDCOM TEXT # Raw GEDCOM record for this individual
i_letter VARCHAR(5) # First letter of the individual's surname
i_surname VARCHAR(100) # Person's surname

pgv_families

f_id VARCHAR(255) # GEDCOM family ID
f_file INT # ID number of the GEDCOM file the record is from
f_husb VARCHAR(255) # ID of the husband
f_wife VARCHAR(255) # 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

s_id VARCHAR(255) # GEDCOM source ID
s_file INT # 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

pgv_other

o_id VARCHAR(255) # GEDCOM record ID
o_file INT # ID number of the GEDCOM file the record is from
o_type VARCHAR(20) # Type of GEDCOM record
  1. (REPO, ADDR, NOTE, OBJE, etc)
o_GEDCOM TEXT # Raw GEDCOM record for this item

pgv_names:
n_gid VARCHAR(255) # Individual ID that this name corresponds to
n_file INT # ID number of the GEDCOM file the record is from
n_name VARCHAR(255) # Name in GEDCOM format,
  1. with / / around the surname
n_letter VARCHAR(5) # First letter of the surname
n_surname VARCHAR(100) # Surname for this name record
n_type VARCHAR(10) # Type of name,
  1. P = primary, A = additional, C=calculated

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
  1. 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.
  1. 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
  1. 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,
  1. 0 is the country or state
p_parent_id INT(11) # ID of this item's parent place in the
  1. hierarchy. A city's parent would be the
  2. county it is in, a county's parent would be
  3. a state or province, and a state or province
  4. 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

u_username VARCHAR(30) # User name
u_password VARCHAR(255) # Encrypted password
u_fullname VARCHAR(255) # User's full name
u_GEDCOMid TEXT # Serialized array representing the GEDCOM IDs
  1. for this user
u_rootid TEXT # Serialized array representing the root IDs
  1. for this user
u_canadmin ENUM('Y','N') # Is the user an admin or not
u_canedit TEXT # Serialized array indicating the editing
  1. privileges a user has for each GEDCOM
u_email TEXT # Email addres
u_verified VARCHAR(20) # User self verified
u_verified_by_admin VARCHAR(20) # User has been verified by the admin
u_language VARCHAR(50) # User's preferred language
u_pwrequested VARCHAR(20) # User requested a new password
u_reg_timestamp VARCHAR(50) # Registration timestamp
u_reg_hashcode VARCHAR(255) # Self-registration hash key
u_theme VARCHAR(50) # User's preferred theme
u_loggedin ENUM('Y','N') # User's login status
u_sessiontime INT(14) # User's last login time stamp
u_contactmethod VARCHAR(20) # User's preferred method of contact
u_visibleonline ENUM('Y','N') # Whether or not the user is visible in
  1. the logged on users block
u_editaccount ENUM('Y', 'N') # Whether or not the user can edit his
  1. own account information
u_defaulttab INT(10) # Default tab on the individual page
  1. for this user
u_comment VARCHAR(255) # Admin's comments on this user
u_comment_exp VARCHAR(20) # Alert date for the admin, for instance
  1. for temporary accounts.
u_sync_gedcom VARCHAR(2) # If the user has a GEDCOM record ID, then
  1. should some of the data for the user (name,
  2. email) be synchronized with the GEDCOM data.
u_relationship_privacy VARCHAR(2) # Should this user use relationship privacy
u_max_relation_length INT # The maximum path that the user is allowed to see
u_auto_accept VARCHAR(2) # Are changes made by this user automatically
  1. accepted into the database

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)
 

phpGedView DATABASE TABLE LAYOUT


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:

TableDescription
pgv_blocksDescription of each user's Portal pagebitweaver modules
pgv_datesStores decoded date information from GEDCOM records
pgv_familiesAll the families in the GEDCOM
pgv_favoritesStores users favoritesbitweaver user prefs
pgv_individualsAll the individuals in the GEDCOM
pgv_linkLinks between gedcom records
pgv_mediaList of media references from gedcom dataThese will be stored using LibertyMime
pgv_media_mappingLinks to gedcom records using media items
pgv_messagesMessages to and from usersbitweaver messaging
pgv_mutexDatabase managed lock on file updates
pgv_namesStores decoded name information from GEDCOM records
pgv_newsStores news items for the Index and Portal pagesvarious other packages
pgv_nextidTable of id numbers fore adding records to gedcom dataThis is not multi-user safe!
pgv_otherAll other level 0 GEDCOM records (i.e., repositories, media objects, notes, etc.)
pgv_placelinksCross-reference between places and individuals and families
pgv_placesPlace hierarchy
pgv_remotelinksTable of urls used within gedcom data
pgv_site_settingsSystem settings dataThese are stored in kernel_config
pgv_sourcesAll the sources in the GEDCOM
pgv_usersTable for user data (replaced by users_users)BitUser


More details can also be found in the phpgedview wiki ( links will be created when their wiki is working again )

Core data tables

The data 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. The entire original gedcom data is stored across these six tables.

pgv_individuals

FieldSizeNotes
i_idVARCHAR(20)GEDCOM individual ID
i_fileSMALLINTID number of the GEDCOM file the record is from
i_rinVARCHAR(20)Individual's RIN number
i_isdeadINTAlive/dead status of individual -1 = not calculated yet 0 = alive 1 = dead
i_sexCHAR(1)Alive/dead status of individual
i_gedcomTEXTRaw GEDCOM record for this individual


pgv_families

FieldSizeNotes
f_idVARCHAR(20)GEDCOM family ID
f_fileSMALLINTID number of the GEDCOM file the record is from
f_husbVARCHAR(20)ID of the husband
f_wifeVARCHAR(20)ID of the wife
f_chilTEXTList of children IDs, semi-colon (;) delimited
f_gedcomTEXTRaw GEDCOM record for this family
f_numchilINTNumber of children in this family


pgv_media

FieldSizeNotes
m_idINT
m_mediaVARCHAR(20)
m_extVARCHAR(6)
m_titlVARCHAR(255)
m_fileVARCHAR(255)
m_gedfileSMALLINT
m_gedrecBLOB

This will be complimented with a link the generic bitweaver content and use the bitweaver management tools.

pgv_media_mapping

FieldSizeNotes
mm_idINT
mm_mediaVARCHAR(20)
mm_gidVARCHAR(20)
mm_orderINT
mm_gedfileINT
mm_gedrecBLOB


pgv_sources

FieldSizeNotes
s_idVARCHAR(20)GEDCOM source ID
s_fileSMALLINTID number of the GEDCOM file the record is from
s_nameVARCHAR(255)Abbreviated title of the source
s_gedcomTEXTRaw GEDCOM record for this source
s_dbidCHAR(1)?


pgv_other

FieldSizeNotes
o_idVARCHAR(20)GEDCOM record ID
o_fileSMALLINTID number of the GEDCOM file the record is from
o_typeVARCHAR(5)Type of GEDCOM record (REPO, ADDR, NOTE, OBJE, etc)
o_gedcomTEXTRaw GEDCOM record for this item


Record detail tables

Date then extracted from the gedcom is processed to provide a more searchable indexing of names and places

pgv_names

FieldSizeNotes
n_idVARCHAR(20)Individual ID that this name corresponds to
n_file SMALLINTID number of the GEDCOM file the record is from
n_numINTVersion number of the name ( allow multiple names )
n_typeVARCHAR(15)Type of Name
n_fullVARCHAR(255)Name in GEDCOM format, with / / around the surname
n_listVARCHAR(255)First letter of the surname
The following fields are only used in INDI records
n_surnameVARCHAR(255)Surname for this name record
n_surnVARCHAR(255)Surname used for soundex
n_givnVARCHAR(255)Given used for soundex
n_soundex_givn_stdVARCHAR(255)Standard soundex given name
n_soundex_surn_stdVARCHAR(255)Standard soundex surname name
n_soundex_givn_dmVARCHAR(255)DM soundex given name
n_soundex_surn_dmVARCHAR(255)DM soundex surname name


pgv_dates

FieldSizeNotes
d_daySMALLINTThe day of month for this date
d_monthVARCHAR(5)The 3 letter abbreviation for month of year
d_monSMALLINTInteger 1-12 for the month of year
d_yearSMALLINTThe year for this date
d_julianday1INTDay count in julian format
d_julianday2INTDay count in julian format
d_datestampTIMESTAMPAdd UTC normalised time information
d_factVARCHAR(15)The fact that this date was associated with
d_gidVARCHAR(20)The gedcom XREF ID where this fact and date were found
d_fileSMALLINTThe gedcom file id where this fact was found
d_typeVARCHAR(13)Used if this date uses an alternate calendar type


pgv_places

FieldSizeNotes
p_idINTUnique identifier
p_placeVARCHAR(150)Place name
p_levelINTLevel of the place in the hierarchy, 0 is the country or state
p_parent_idINTID 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_fileSMALLINTID number of the GEDCOM file the record is from
p_std_soundexVARCHAR(255)Standard soundex of place name
p_dm_soundexVARCHAR(255)DM soundex of place name

At some point a cross reference to openstreetmap will be added along with links to any mapper content.

Crosslink tables

Data is then further indexed to provide cross references between the data.

pgv_placelinks

FieldSizeNotes
pl_p_idINTUnique identifier
pl_gidVARCHAR(20)Family or individual ID referencing this place
pl_fileSMALLINTID number of the GEDCOM file the record is from


pgv_link

FieldSizeNotes
l_fileSMALLINTID number of the GEDCOM file the record is from
l_fromVARCHAR(20)Source reference ID
l_typeVARCHAR(15)Link type
l_toVARCHAR(20)Destination reference ID


pgv_nextid

FieldSizeNotes
ni_idINTID number for record type
ni_typeVARCHAR(15)Record type
ni_fileSMALLINTThe gedcom file id where this id is used

Since MAX() is used to access this data, it is possible in situations where multiple users are adding data that the same ID will be assigned. This should be locked in a similar way to the file updates.
The numbers generated are used in conjunction with the the prefix letters allocated to each record type

pgv_remotelinks

FieldSizeNotes
r_gidVARCHAR(20)Reference ID for link
r_linkidVARCHAR(255)Link data
r_fileSMALLINTID number of the GEDCOM file the record is from


Miscellaneous Tables

The remaining tables provide additional user data and is on the whole replaced by other bitweaver packages.

pgv_blocks

This is replaced by the bitweaver layout manager phpgedview blocks are repackaged as modules.

pgv_favorites

FieldSizeNotes
fv_idINTRecord ID
fv_usernameVARCHAR(30)User name whom the favourite belongs to
fv_gidVARCHAR(20)ID of the favourite
fv_typeVARCHAR(15)Type of favourite (currently only INDI)
fv_fileVARCHAR(100)File that this favourite belongs to
fv_urlVARCHAR(255)The URL for this favourite if it is not one of the basic types
fv_titleVARCHAR(255)A title for URL based favourites
fv_noteTEXTOptional descriptive information about this favourite

This should be provided by a similar facility in bitweaver, but may need to develop that area first.

pgv_messages

Replaced by bitweaver messaging system.

pgv_news

Replaced by bitweaver articles or blog depending on the type of user news display required.

pgv_users

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

PhpgedviewPackage
Page History
Date/CommentUserIPVersion
16 Sep 2009 (08:37 UTC)
Lester Caine81.138.11.1368
Current • Source
Lester Caine81.138.11.1366
View • Compare • Difference • Source
Lester Caine81.138.11.1365
View • Compare • Difference • Source
Lester Caine81.138.11.1364
View • Compare • Difference • Source
Lester Caine81.138.11.1363
View • Compare • Difference • Source
Lester Caine81.138.11.1362
View • Compare • Difference • Source
Lester Caine81.138.11.1361
View • Compare • Difference • Source