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_link | Links between gedcom records | |
pgv_media | List of media references from gedcom data | These will be stored using LibertyMime |
pgv_media_mapping | Links to gedcom records using media items | |
pgv_messages | Messages to and from users | bitweaver messaging |
pgv_mutex | Database managed lock on file updates | |
pgv_names | Stores decoded name information from GEDCOM records | |
pgv_news | Stores news items for the Index and Portal pages | various other packages |
pgv_nextid | Table of id numbers fore adding records to gedcom data | This is not multi-user safe! |
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_remotelinks | Table of urls used within gedcom data | |
pgv_site_settings | System settings data | These are stored in kernel_config |
pgv_sources | All the sources in the GEDCOM | |
pgv_users | Table for user data (replaced by users_users) | BitUser |
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
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_media
Field | Size | Notes |
m_id | INT | |
m_media | VARCHAR(20) | |
m_ext | VARCHAR(6) | |
m_titl | VARCHAR(255) | |
m_file | VARCHAR(255) | |
m_gedfile | SMALLINT | |
m_gedrec | BLOB | |
This will be complimented with a link the generic bitweaver content and use the bitweaver management tools.
pgv_media_mapping
Field | Size | Notes |
mm_id | INT | |
mm_media | VARCHAR(20) | |
mm_gid | VARCHAR(20) | |
mm_order | INT | |
mm_gedfile | INT | |
mm_gedrec | BLOB | |
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 |
Record detail tables
Date then extracted from the gedcom is processed to provide a more searchable indexing of names and places
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
Field | Size | Notes |
d_day | SMALLINT | The day of month for this date |
d_month | VARCHAR(5) | The 3 letter abbreviation for month of year |
d_mon | SMALLINT | Integer 1-12 for the month of year |
d_year | SMALLINT | The year for this date |
d_julianday1 | INT | Day count in julian format |
d_julianday2 | INT | Day count in julian format |
d_datestamp | TIMESTAMP | Add UTC normalised time information |
d_fact | VARCHAR(15) | The fact that this date was associated with |
d_gid | VARCHAR(20) | The gedcom XREF ID where this fact and date were found |
d_file | SMALLINT | The gedcom file id where this fact was found |
d_type | VARCHAR(13) | Used if this date uses an alternate calendar type |
pgv_places
Field | Size | Notes |
p_id | INT | Unique identifier |
p_place | VARCHAR(150) | Place name |
p_level | INT | Level of the place in the hierarchy, 0 is the country or state |
p_parent_id | INT | 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 | SMALLINT | ID number of the GEDCOM file the record is from |
p_std_soundex | VARCHAR(255) | Standard soundex of place name |
p_dm_soundex | VARCHAR(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
Field | Size | Notes |
pl_p_id | INT | Unique identifier |
pl_gid | VARCHAR(20) | Family or individual ID referencing this place |
pl_file | SMALLINT | ID number of the GEDCOM file the record is from |
pgv_link
Field | Size | Notes |
l_file | SMALLINT | ID number of the GEDCOM file the record is from |
l_from | VARCHAR(20) | Source reference ID |
l_type | VARCHAR(15) | Link type |
l_to | VARCHAR(20) | Destination reference ID |
pgv_nextid
Field | Size | Notes |
ni_id | INT | ID number for record type |
ni_type | VARCHAR(15) | Record type |
ni_file | SMALLINT | The 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
Field | Size | Notes |
r_gid | VARCHAR(20) | Reference ID for link |
r_linkid | VARCHAR(255) | Link data |
r_file | SMALLINT | ID 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
Field | Size | Notes |
fv_id | INT | Record ID |
fv_username | VARCHAR(30) | User name whom the favourite belongs to |
fv_gid | VARCHAR(20) | ID of the favourite |
fv_type | VARCHAR(15) | Type of favourite (currently only INDI) |
fv_file | VARCHAR(100) | File that this favourite belongs to |
fv_url | VARCHAR(255) | The URL for this favourite if it is not one of the basic types |
fv_title | VARCHAR(255) | A title for URL based favourites |
fv_note | TEXT | Optional 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