Version 6

phpgedview_tables

Tables used by the source phpgedview package

Created by: Lester Caine, Last modification: 16 Sep 2009 (08:28 UTC) by Lester Caine

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:

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


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