History of phpgedview_tables
!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))
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))