TNGv11 Database Documentation

The Entity-Relationship diagram above shows relationships among TNG database tables. Those relations reflect foreign keys in the tables. The ER diagram and the discussion below use the table entity name to identify each table, though, by default, TNG adds a prefix ("tng_") to the physical table names when it sets up the database. Table names in the discussion are always Capitalized, and fieldnames are always bold.

TNG's database model, like that of most genealogical application, is closely tied to the standard Gedcom model (generally 5.5.1), but it does extend the Gedcom model in several ways, with both table attributes and objects). The object types are typically represented as database tables.

TNG's database is relational; it is implemented with MySQL's MYSAM engine, which does not directly support foreign keys and relational integrity. The foreign keys are thus implemented through the application, not through the database.

Overview of TNG Database Tables and Table of Contents

Tables that implement the Gedcom model (i.e. that can be populated by a Gedcom Import) are bold and marked with an asterisk. The first set of parentheses after a table name is its primary key, as used by foreign keys in the database. If the table has a identity key that is not used as the primary key, then the identity key (if there is one) is listed in the second set of parentheses.

Each table name is also a hyperlink to the section of this document where it is described.

Table Relationship Rules

1. Trees and Branches

   1.1 Trees

   A Tree in TNG is a container for any independent set of family data. TNG allows you to support multiple Trees on your site, but since Trees are independent, you cannot link a person in one tree to any person or family in another tree. For that reason, any people that are or could be linked together should generally be kept in the same tree.

   The Trees table does not have an identity key. Its simple primary key is a string field named gedcom That fieldname comes from the name of the widely-used "Genealogy Data COMmunication" standard that also serves as something of a data model. The use of gedcom in TNG to identify a Tree reflects the notion that a Tree can be imported from or exported to a GEDCOM file. TNG is essentially a superset of GEDCOM. See the TNG Wiki page describing GEDCOM.

   Many of the TNG database tables contain a field named gedcom, which is a foreign key to the Trees table, and indicates which tree that a record belongs to. Most instances of gedcom in the TNG database are part of a composite key that points to a specific Person, Family, Source, Place, etc.

   1.2 Branches

   A Branch is a collection of related People and Families within a tree. Branches can be used to, for example

   • To restrict a User to a Branch, so that the User can either see living people only within that Branch, or perhaps edit only the People and Families within that Branch.
   • To identify a extended family for certain TNG reports.
   • Within the Person Profile page, where all of a Person's branches are listed, to identify a Person's membership in extended family.

   The Branches table is a set of rules that define Branches. A Branch rule starts with a single person, and then identifies

   • The number of generations of direct ancestors to include in the branch,
   • The number of generations of direct descendants to include in the branch,
   • The number of generations of direct descendants from each ancestor to include in the branch, (this captures a certain degree of cousins), and
   • whether to include spouses in the branch.

   When a Branch rule is applied to the tree, the branch field in the People and Families tables is populated with the Branch name (the Branch table primary key). The branch fields are multivalued, since a records can be in more than one Branch. They contain comma-separated Branch names, each of which is a foreign key to the Branch table.

   Branch values in a record are often called "Branch labels", and the process of process a Branch's rule and recording the Branch name in records is called "Branch labeling."

   1.3 Branchlinks

   The Branchlinks table is normalized representation of the multivalued branch field. Its three data fields are all keys that are needed to make the record unique:

   1. branch - The unique Branch name
   2. gedcom - The tree to which the Branch belongs and all records in it belong.
      Note that this database structure allows a Branch name to occur in more than one tree, but (as of TNGv11.0.2) the TNG application does not. unique Branch name
   3. persfamID - Either a personID or a familyID, both of which must be combined with the gedcom to identify a specific Person or Family record. Note that personIDs and familyIDs are always distinct from each other, because they start with a distinct letter (usually, but not necessarily, I and F, respectively).

   The Branchlinks table does have an identity key, persfamID, which is redundant and unused.

   Actually, the Branchlinks table is virtually unused in TNG. The values in the Person and Family branch fields are used when a Branch-restricted user searches for data or tries to retrieve records. The only role of the Branchlinks table in the TNG application (again, as of TNGv11.0.2) is in what is called the "Secondary Processes" after a full Gedcom Import. A full Gedcom Import erases and reloads all of the People and Family data in a Tree, but it doesn't affect the Branchlinks table. The Branchlinks table is used to quickly rebuild the Branch lables for all Branches in the Tree that was just imported. Unfortunately, that quick relabeling process pays no attention to the Branch rule, and thus ignores any new or changed parent/child relationships that would affect membership in a Branch. To generate accurate Branch labels, it is necessary to go to the TNG administrative options, select "Branches", select a branch, and relabel that one branch.

2. People, Families, and Children

   People and Families are the most fundament tables in a Gedcom-related database, and they are quite similar. Most information about People and about Families is recorded as "Events" (which are described in a later chapter).

   2.1 Fields common to the People and Families tables

   The key structure of both the People table and the Families table is:

   • The identity key (a sequential number) is a field named ID, which is never used by TNG.
   • personID and familyID are the tables' Gedcom-style recordIDs, which consists of a single capital letter distinct to the table, and a whole number that is unique within the tree. (You can guess which field goes with which table.)
   • The numeric part of these recordIDs are imported from a Gedcom file exactly as they appear in the Gedcom file.
   • The initial letter in the recordIDs in a Gedcom file are expected to be 'I' and 'F', but TNG has administrative settings that allow a site use any unique letter for each Gedcom record type
   .
   • Records that are created by TNG's data entry operations (i.e. not imported from a Gedcom file) are assigned a Gedcom-style record ID
   • Since the Gedcom recordID's for a given table are only unique within a TNG Tree, the effective primary key for each table is a composite key consisting of that table's Gedcom recordID (personID or familyID and the gedcom field, which is also a foreign key to the Trees table.

   Other fields that both tables have in common include:

   • living – A 0/1 flag that indicates whether the person is believed to be alive. Its value can be determined TNG system paramters, and it appears as a checkbox on TNG's Person and Family edit screens.
   • private – A 0/1 flag that limits what users can see a Person or Family record, or even a Person's name.
   • branch – A multi-valued (comma-separated) list of Branch names that define what Branches the Person or Family is part of.
   • changedby – The username (a foreign key to the Users table) of the last User who changed the record.
   • edituser – Another username that seems to come into play only when there is an edit conflict between two simulaneous users.
   • edittime – A date/time stamp of the last time the record was edited.

   2.2 People

   In addition to the fields described above, a Person record contains:

   • Several name components such as firstname, lastname, and nickname
   • sex, which can take on any single-letter value. Generally, it is expected to be 'M','F', or 'U' (for 'Unknown')
   • Seven specific "Built-in" Events, which are listed and explained in the section on Events.
   • sex, which can take on any single-letter value. Generally, it is expected to be 'M','F', or 'U' (for 'Unknown')
   • nameorder, which can override the TNG system value of the same name, which determines how name components are combined into a full name.
   • famc, the familyID of the first Family in which this Person is a child.
   • metaphone, a particular sort of phonetic version of the Person's last name, which can be used in searches.

   2.3 Families

   To Gedcom, and to TNG, a Family is defined as two people (who do not actually have to be known) who are either married or who have children. In addition to the fields listed in section 2.1 above, the Families table contains

   • husband and wife contain personID's and, in combination with the Family record's gedcom, serve as foreign keys to the People records for the two partners in the family.

   • husborder and wifeorder are numbers that control the order of the husband's and the wife's marriages. They are set, based on marriage date (but not chilren's birth dates) by a Gedcom Import Secondary Process called "Sort Marriages" , and can be managed in the TNG program that edits Person records.

   • marrtype, a short text field that some genealogists would populate with values such as 'Civil', 'Religious', and 'Common Law'.
   • status. This short text field could be intended to indicate whether the couple was married, but I can't tell if this field is ever used.
   • Three specific "Built-in" Events, which are listed and explained in the section on Events.

   2.4 Children

   The Children table is primarily a linking table that identifies the Children of a given Family. It is composed of:

   • ID, the identity key, which is never used.
   • The effective primary key consists of
     • gedcom, the tree the family is in.
     • familyID, the Family record in which the parents are identified
     • personID, the Person record of a child in the family
   • The fields frel and mrel contain the "relationship type" of the childs's relationship to the two parents, e.g. 'Natural', 'Birth', 'Adopted', etc. There is no dictionary of relationship types.
   • ordernum orders the children in a family. The order is initialized by a Gedcom Import, though child order in Gedcom is not especially reliable. The order can be set algorithmically by a Gedcom Import Secondary Process called "Sort Children" (which always sorts children with no birth date to the end), and can be managed in the TNG program that edits Family records.
   • haskids is a flag that is set by the Gedcom Import Secondary Process called 'Track Lines' and the Family record editor. Is allows the TNG Person and Family Profiles to display a flag next to children who have children without having to do an extra query to find the children's childen.
   • parentordernum orders the families of a child who is in more than one family (typically through adoption).
   • One "Built-in" Event, the LDS Child/Parent Sealing Ordinance. Built-in Events are discussed in the section on Events.

   2.5 Associations

   The Associations table defines arbitrary connections or relationships between People, or between a Person and a Family. The Gedcom standard allows only person-to-person relationship, but TNG expands that to person-to-family.

   The direction of a relationship is not necessarily obvious at a glance. In TNG, the Person Profile presents an Association in tabular form, essentially like this (for say, a Godparent relationship in John Doe's record):
   Association: Jane Roe (Relationship: Godparent)

   Which is the Godparent, and which is the Godchild? Gedcom defines it this way: Person 1's relationship is Person 2. So John Doe's Godparent is Jane Roe, and Jane Roe is the Godparent. That won't be clear in the TNG Person Profile, but that's the standard meaning.

   Some relationships, such as 'Close Friend', or 'Next-door Neighbor', work in both directions. For some other relationships, such 'Godparent', the reverse relationship - 'Godchild' - is straightforward. But for other relationships, such as 'Maid of Honor', the reverse relationship is very hard to define using the Gedcom guideline. If you define the 'Maid of Honor' relationship in Betty Jo's record, and point it to Mary Sue, then Mary Sue was Betty Jo's Maid of Honor. But there's just no way to say that in possessive terms in Mary Sue's record. There is no standard solution, but it may be reasonable to interpret relationships that end in 'of' to be reverse relationships. That is, in Mary Sue's record, the relationship
   Association: Betty Jo (Relationship: Maid of Honor of)
   could be interpreted to mean Mary Sue was the Maid of honor of Betty Jo. Again, that's not clear from the TNG Person Profile, but that could be the interpretation.

   The fields of the Associations table are:

   • assocID – The identity key.
   • gedcom – The Tree to which both associated records belong.
   • personID – The first person in the Association.
   • passocID – The personID or familyID for the second record in the association.
   • reltype – 'I' or 'F', to identify the record type of the second record in the Association.
   • relationship – A 75 character free text field to identify the Association. TNG does not use a dictionary or look up existing relationships.

3. Places and Cemeteries

   3.1 Places

   Although they are GEDCOM entities, Places are not numbered GEDCOM records like People, Families, Sources, and Repositories. As with People, Families, Sources, and Repositories, the Places table has an identity key called ID that is not used. The primary key of the Places table is a two-field composite key consisting of gedcom (the tree ID) and place (the full place name). Place numbers – the identity key in the field named ID - are never used.

   Note that this scheme represents a Relational Database de-normalization, as the full place names are stored in multiple places in the database. It is possible for a user to edit a place name without changing all of the foreign keys that point to that place name.

   Places are also different in that Places do not have to belong to a tree. The TNG site-configuration variable, $places1tree, has the value 1 if places are not assigned to trees. That is, when $places1tree==1, the gedcom value in the Places table is always empty, and trees share place names. With $places1tree==0, if multiple trees have events in, say, "Dallas, Dallas County, Texas", then that Place name will occur multiple times in the places table, and each record will be related to a different tree.

   3.2 Cemeteries

   Cemeteries are a TNG concept; not a Gedcom concept or object. In Gedcom and TNG, the Place associated with a Burial event is typically (not always) a Cemeteriy, Graveyard, Mausoleum, or the like. Those burial places can have attributes (particularly plots and headstones that are not associated with the other events in the Places table.

   It is significant to note that it is common for genealogy researchers to include the cemetery (graveyard, etc) in a burial place name, but not common to the hospital or other facility in birth or death place names. A hospital or other facility might be record in a birth or death Event's agency field, or in a Note attached to the Event, but somehow burial places are treated differently.

   The TNG Cemetery table allows TNG users to keep track of headstone photos in a cemetery and the grave plots associated with that photo, and makes it easy to focus on specific Cemeteries that are of particular interest to the TNG user. Each TNG Cemetery is tied to a Place record (not just to a town or county, but to a place name that identifies a cemetery, graveyard, etc.)

   Unlike Place records, where the Place name is a string that includes the facility, town, county, state, and country, TNG Cemetery records break out the components of the place name into fields named cemname, city, county, state, and country.

   See the TNG Wiki's Cemeteries page

   3.3 Countries and States

   These two tables are one-field lookup tables for the Countries and States fields in the Cemetery table. There is just one big list of States that is not tied to Country.

4. Events

   4.1 What Events and Eventtypes are

   TNG, as with most genealogical applications, describes objects such as people and families with "events" rather than single-valued "fields" or "attributes". See the TNG Wiki article on Events in TNG and in Gedcom for a description of events, and the differences in the ways Gedcom and TNG characterize what we think of as "events".

   In TNG, Events are (generally) stored as database records in the Events table. These records are linked to a Person, a Family, or even (in two specific cases), a Repository. Events describe some aspect or characteristic of that Person, Family, or Repository. Events are distinguished by their "Eventtype", such as "Birth", "Death", "Marriage", or "Occupation". Most "Eventtypes" have a unique "Tag" such as "BIRT", "DEAT", "MAR", or "OCCU", which is almost always expressed as 3 or 4 uppercase characters. The terms "Eventtype" and "Tag" are often used interchangeable to identify the meaning of an Event.

   Each Eventtype, even those that clearly occur only once in real life, such as Birth and Death, can occur more than once in a (logical) Person or Family record. With Eventtypes such as Birth and Death, multiple occurrences reflect multiple opinions (likely from different sources) about the Event date and/or Place. On the other hand, with Eventtypes such as Occupation, Residence, or Census (appearance in a Census), multiple Events of the same Eventtype are more likely to reflect the multiple real-life occurrences of such an Event. There is nothing in the in the Eventtype or Event data to distinuish between Eventtypes that naturally occur more than once, and those that occur more than once only because of different opinions of the data values.

   4.2 "Built-In" Events

   As first installed, TNG saves only few types of Events into its database. They are known as "Built-in" Eventtypes. (They are and sometimes referred to as Built-in "Events", though there is a diffence in meaning between Eventtypes and Events (i.e. Event occurrences). The Date and Place associated with each Built-in Event occurence are stored directly in the People, Families, or Children tables - not in separate Event records. TNG's Built-in Eventtypes are:

   1. People Table:
      • Sex
      • Primary Name
      • Primary Birth
      • Primary Death
      • Primary Christening (aka Alternate Birth)
      • Primary Burial
      • Primary LDS Baptism
      • Primary LDS Confirmation
      • Primary LDS Initiation
      • Primary LDS Endowment
   2. Families Table:
      • Primary Marriage
      • Primary Divorce
      • Primary LDS Marriage Sealing
   3. Children Table:
      • Primary LDS Parent/Child Sealing

   "Primary" means the first or most important or most likely Event of that type associated with that Person, Family, or Children record. These Built-In Eventtypes at least ostensibly occur only once in a Person's life or for a "Family", and only one occurrence of each of these Eventtypes can be stored in a Person or Family record. Secondary occurrences of Built-in Events are must be defined as "Custom" Events (see section 4.4).

   Note that the descriptive name (Birth, Death, Marriage, etc.) and Gedcom Tag (BIRT, DEAT, MAR, etc.) for each built-in Eventtype are are hard-coded into TNG. They cannot be changed without changing TNG code, and additional Built-in Events cannot be added without changing TNG code.

   How TNG Stores Built-in Event data:

   • A Person's Sex is stored in a Person record as a one-character value in the field named Sex.
   • A Person's name is broken down into the database fields title, prefix, firstname, lastname, suffix, and nickname.
   • The other built-in Events occupy three fields each in a Person, Family, or Child record:
     1. {prefix}date – An external representation of the event date (e.g. 10 Mar 1841, Apr 1888, Abt 15 Sep 1923, etc.)
     2. {prefix}datetr – An internal, sortable, representation of the event date (e.g. 1841-03-10, 1841-03-00, 1923-09-15, respectively.), and
     3. {prefix}place – The event place
     Where {prefix} is "birth", "death", "alt", "buial", "conf", "init", etc. The fieldnames for Birth, for example, in a Person record are thus birthdate, birthdatetr, and birthplace. Note that these prefixes are based on the corresponding Gedcom Tag, but are lowercase, and aren't always abbreviated exactly like the Tags are.

   (At this point discussion of the remaining attributes of built-in events will be deferred to Section 4.5)

   4.3 "Custom" Events and the Events Table

   All Events not considered "Built-in" to TNG are called "Custom Events", and are stored in the Events table. This would include, for example:

   • Secondary occurrences of Built-in Events (birth, death, marriage, divorce, etc), except for the LDS Parent/Child Sealing ordinance. (There is no provision for custom event in a Children, but then again, it's not likely at all that one would need to define two Events for the same Parent/Child Sealing ordinance.)
   • All occurrences of all other Events - Residence, Education, Military, Occupation, etc., etc.

   It is significant to note that the word "Custom" applies to the use of these Eventtype Tags in TNG, not in GEDCOM. Most "Custom" Eventtypes Tags in TNG databases are defined in the Gedcom standard and are not "Custom" from Gedcom's perspective. To TNG, all Eventtypes not defined as Built-in are, by definition, "Custom", whether they are defined by Gedcom or not.

   Event records have the follow fields:

   • eventID – the identity key.
   • gedcom and persfamID to identify the Person, Family, or Source record to which the event belongs.
   • eventtypeID to identify the Event's type (its meaning).
     • For Custom Events, this field's value is a foreign key to the Eventtypes table, which is a dictionary of Eventtypes.
     • As you will see below, Event records can, but do not always exist for Built-in Events. In these Built-in Event records, the eventtypeID field holds the Event Tag (e.g. 'BIRT', 'DEAT', 'MAR', 'DIV', etc).
   • eventdate, eventdatetr, and eventplace, corresponding to the date and place fields that describe built-in events.
   • age – (for Person events) – the person's age at the time of the event. The age field is populated only if it is provided either through data entry or a GEDCOM file. That is, it is not automatically calculated and stored.
   • agency – a facility, institution, or similar entity associated with the event. This might be a hospital in which a birth or death occurred (or "home" or "at home"), or the name of a educational institution for a Graduation or Education event. In practice, however, "agencies" are typically identified in the Descriptive Text or Notes associated with an Event, rather in the agencyfield.
   • cause – typically, cause of death.
   • addressID – a foreign key to the Address table, where an Address associated with the event would be stored.
   • info – the Event Value (e.g. an occupation, a religious affiliation, the number of marriages) or "Descriptive Text" associated with the Event. (In the TNG Person and Family edit screens, this field is is labeled as "Detail" rather than "Info" or "Descriptive Text".

   Note that Custom Events cannot be stored in a TNG database unless or until an Eventtype record that defines their descriptive name and Gedcom Tag is stored in the Eventtypes table. Fortunately, a TNG site administrator can set a Gedcom Import option that adds a new Eventtype record for every new Eventtype seen during a Gedcom Import. But for TNG sites where all TNG data is entered manually, the site administrator will also have to enter all Custom Eventtypes manually.

   4.4 The Eventtypes table

   The Eventtypes table is just a dictionary - a lookup table with information about Eventtypes. It does contain any data that is specific to People, Families, Trees, or any other Gedcom object (except Events.) Its fields are:

   • eventtypeID – the identity key
   • tag – the Event abbreviation used in Gedcom files (BIRT, DEAT, OCCU, RACE, _MILI, etc)
   • display – The Event name that is shown in TNG data entry screens and reports (Birth, Death, Occupation, Race, Military, etc)
   • type – A single letter indicating what type of record the Eventtype is associated with (I, F, or R).
   • keep – Essentially an "active" flag.
   • collapse – A flag that is used in the TNG Person Profile screen to determine whether to display all Events of this Eventtype, or compress them into one line that can be expanded.
   • ordernum – This value is used in the TNG Person Profile screen to allow all Events of a given Eventtype to be displayed before Events of a different Eventtype.

   4.4 Built-In Events with age, agency, cause, or address value

   When built-in events (i.e. the primary occurrences of Birth, Death, etc.) have an age, agency, cause, or address value, TNG builds a "dummy" Event record that stores these ancillary fields but not the date and place, which remain only in the Person or Family table.

   Dummy event records necessarily have an eventID value, since eventID is the table's identity key. But the eventtypeID field in dummy Event records contains the tag of the built-in Event ("BIRT", "DEAT", "MAR", etc.) rather than a foreign key to the Eventtypes table, and it is generally necessary for PHP code rather than MySQL queries to use the Eventtype tag plus the PersonID or FamilyID from the persfamID field to find the appropriate Event date and place fields in the Person or Family record.

   4.5 Built-In Events and Descriptive Text

   As described in the TNG Wiki article on Events in TNG and in Gedcom, Gedcom actually has three types of what we think of as Events - "Attributes", "Ordinances", and "Events". (Yes, confusingly, in Gedcom, what we generally think of an an Event is really just one type of Event.) Gedcom "Attributes" (such as Occupation, Religious Affiliation, Number of Children) have an "Attribute Value", which winds up in TNG as "Descriptive Text", or the Event info. But, technically "Ordinances" cannot have descriptive text, and the only descriptive text value that Gedcom "Events" can have is 'Y', for 'Yes, the Event occurred, but the date and place are unknown'.

   TNG does not specifically distinguish among Gedcom "Events", "Attributes", and "Ordinances" - they are all Events to TNG. But TNG does effectively implement an aspect of that distinction in that

   1. All of the Gedcom Ordinances are implemented in TNG as Built-in Events, which cannot have descriptive text.
   2. The other TNG Built-in Events happen to be Events, so the absence of descriptive next is not really a problem. For death Events, TNG does consider a date value of 'Y' to mean that the person is known to have died, and displays 'Yes, Date unknown' in some reports, when it sees a death date of 'Y'.

   All Custom Events, including secondary occurrences of Built-in events can have Descriptive Text.

   4.6 Temp_Events

   The Temp_Events table is is used to implement TNG's "Suggest Changes" feature. When a user suggests a change to TNG data, the suggested data is saved in the form of an event in a Temp_Event record. As a result, TempEvents looks much like the Events table, with fewer fields. It links to existing Event records via the eventID field, which can hold an Event ID (for Custom Events) or a Tag (for Built-in Events). However, it uses separate personID and familyID fields rather than an overloaded persfamID field.

5. Notes

   5.1 'Notes', in TNG, overall

   Let's start with the abstract notion of a note as arbitrary text of essentially unlimited length that is associated with some object - a Person, Family, Source, Event, etc.

   In TNG,

   1. The Media, Place, Cemetery, DNAtest, and User tables each have a text field named notes. A MySQL text field can be a large as 65,655 characters. In each of these tables, a record can have exactly one occurence of a "Notes" value. It is important to add that the Media notes value is taken from the Gedcom tag "TEXT" in a Gedcom media record, not from a "NOTE" tag. And none of the other notes fields can come from a Gedcom import.
   2. The TNG Notes table holds notes that are associated with an Event, a Person, a Family, or a Source.a field named note. Each Notes record represents a note that can be associated with a Person, Family, Source, or Event. Each Person, Family, Source, or Event may have more than one Note, so there is a TNG Notelinks table to indicate which Note belongs to which Person, Family, Source, or Event.

   When we speak of a Note or Notes in the context of TNG and Gedcom files, we are essentially always referring to this second meaning.

   5.2 The Notes table, which is (usually) actually named 'Xnotes'

   TNG Notes can originate as Gedcom NOTE records that are subordinate to People, Families, Sources, or Events, or can be entered through the TNG application. The TNG Notes table is composed of these fields:

   1. ID, the identity key, and the only unique key in the table.
   2. gedcom, the tree to which the note belongs. It may be more accurate to say that notes belong to an Event, Person, Family, or Source, and the Note record's gedcom relates to the Tree to which the Event, Person, Family, or Source belongs.
   3. noteID - a Gedcom noteID. Only some Gedcom notes are zero-level records with a noteID. But most Gedcom notes (that are saved as TNG notes) are immediately subordinate to Gedcom Person, Family, Source, or Event records and do not have a noteID.
   4. note an arbitrarily-long text field that contains the text of the note.

   Notes that are entered through TNG's data entry operations are never assigned noteID's, and TNG cannot assign a note to more than one object. So the notion of a Gedcom noteID is preserved in TNG, but is basically irrelvant.

   5.3 Notelinks

   The Notelinks table is used to tie a Note to a Person, Family, Repository, or Event. As a linking table, the Notelinks table theoretically allows Notes to be linked to any number of objects, but the TNG application does not provide for such links. Notes can be used only once.

   The Notelinks table contains the foreign key xnote to refer to a Note, and uses the following foreign key fields to link to the record (a Person, Family, Repository, or Event) that a Note belongs to:

   • The foreign key to a Person, Family, or Repository is the two-field (gedcom, persfamID) composite key, where persfamID can be a PersonID, a FamilyID, or RepositoryID.
   • The foreign key to a Event (whether a Person or Family event) is the three-field composite key
     1. gedcom, the Trees primary key,
     2. persfamID), a PersonID or a FamilyID, and
     3. eventID
        1. For Custom Events, this field contains the primary key of an Event record.
        2. For Built-In Events, this field contains the Gedcom tag ('BIRT','DEAT','MAR', etc.) of the built-in Event

   Some additional details:

   • When a note is associated directly with a Person, Family, or Repository, and not an Event, eventID must be 0 (zero).
   • For Custom events, the eventID is sufficient to identify an Event, which, also contains a (gedcom, persfamID) foreign key to the Person or Family.
   • For Built-In Events, the eventID does not link to a record, and must be processed by TNG code to find the specific set of fields in the Person or Family table associated with that event, plus the possible "dummy" Event records that contains additional fields for that event.

6. Sources, Repositories, and Citations

   In TNG, Sources, Repositories, and Citations are straightforward representations of their corresponding Gedcom records.

   6.1 Fields common to the Sources and Repositories tables

   Sources and Repositories are "level zero" Gedcom records, and their tables have the same key structure in TNG as People and Families:

   • The identity key (a sequential number) is a field named ID, which is never used by TNG.
   • sourceID and repoID are the tables' Gedcom-style recordIDs, which consist of a single capital letter distinct to the table, and a whole number that is unique within the tree. (You can guess which field goes with which table.)
   • The numeric part of these recordIDs are imported from a Gedcom file exactly as they appear in the Gedcom file.
   • The initial letter in the recordIDs in a Gedcom file are expected to be 'S' and 'R', but TNG has administrative settings that allow a site use any unique letter for each Gedcom record type
   .
   • Records that are created by TNG's data entry operations (i.e. not imported from a Gedcom file) are assigned a Gedcom-style record ID
   • Since the Gedcom recordID's for a given table are only unique within a TNG Tree, the effective primary key for each table is a composite key consisting of that table's Gedcom recordID (sourceID or repoID and the gedcom field, which is also a foreign key to the Trees table.

   6.2 Sources

   Sources have a Notes attribute, which is implemented through the Notes and Notelinks tables so that a Source may have have multiple Notes. Neither Notes nor links to the Notes table appear in the Sources table. The relationship runs the other way; that is, the Notelinks table links to the Sources table.

   Sources also have a repoID field, which, when combined with the gedcom field, forms a foreign key to the Repository record that indicated where the source can be found.

   6.3 Repositories

   Respositories have:

   • A Notes attribute, which is implemented through the Notes and Notelinks tables so that a Repository may have have multiple Notes,
   • Email and phone attributes, which are implemented as TNG Events, which means that there can be multiple email address and phone numbers for one Repository.
   • An address attribute, which is implemented in the Repositories table as the addressID field, a foreign key to the Addresses table.

   6.4 Citations

   Citations describe the use of a Source to supply data to an Event. Though 'Citation' is a GEDCOM concept, Citations are not identified as numbered objects in Gedcom; they are simply part of an Event. Since an Event can have multiple Citations, TNG implements Citations as a table that links to an Event.

   Citation records use gedcom and sourceID as a foreign key to the Source.

   To link to an Event, Citions have to use the same scheme as Notes; that is:

   • For Custom Events, eventID is the foreign key to an Event record.
   • For Built-in Events, eventID is a Built-in Event Tag that must be processed by PHP logic, and the composite foreign key (gedcom, persfamID) links to the Person or Family record where the Built-In Event is stored.

   In theory, each Citation should be specific to single Eevent, and describe the exact text in a Source that supplies or supports the data in that Event. But, in practice, many Citations are less specific than that, and either refer to the Source without more detailed specification of the text that supports the Event, or refers to text in the Source that supports multiple Events. In other words, many Citations are identical to each other, not only for a specific Person, but sometimes a citation (e.g. 'John Doe's Obituary') supports Events in more than one Person or Family.

   Some Genealogy application take advantage of this duplication of Citations, and create Citation objects that can be assigned to multiple Events. But neither Gedcom nor TNG does that. Each individual citation refers to exactly one Event, and to exactly one Source.

   Conveniently, the TNG Person Profile page does combine identical Citations for a Person into a single entry in its list of "Sources" (which are really "Source Citations").

7. Media

   7.1 The Media table

   Media Items are a fundamental Gedcom concept, and are usually defined in Gedcom files as "level zero" records. TNG Media Items are conceptually just the same as Gedcom Media Items.

   In TNG, Media Items are defined in the Media table. Like Notes and unlike the People, Families, Sources, Repositories, the TNG Media table does not use the Gedcom mediaID as its primary key. Instead,

   • mediaID is the identity key (an auto-incremented integer) of the Media table. It used as the foreign key value when other records link to a Media record,
   • The Gedcom ID of Media Items is imported into the mediakey field, and
   • Media records that are entered outside of a Gedcom Import are not assigned a Gedcom-style ID. Instead the Media Item's filename (if there is one) is stored in the mediakey field.

   7.2 Medialinks

   Through the Medialinks table, one Media Item can be associated with any number of People, Families, Events, Sources, Repositories, or Places. Like the Notelinks table, the Medialinks table contains a foreign key to link to its parent record. In this case that foreign key is mediaID to refer to a Media item.

   In the Medialinks table (much like in the Notelinks table)

   • The foreign key to a Person, Family, Source, or Repository is the two-field (gedcom, personID) composite key, where persfamID can be a PersonID, a FamilyID, a SourceID, or a RepositoryID.
   • The foreign key to a Event (whether a Person or Family event) is the three-field composite key
     1. gedcom, the Trees primary key,
     2. personID), a PersonID or a FamilyID, and
     3. eventID
        1. For Custom Events, this field contains the primary key of an Event record.
        2. For Built-In Events, this field contains the Gedcom tag ('BIRT','DEAT','MAR', etc.) of the built-in Event

   There are three important differences between the ways Notelinks and Medialinks link to other records, however:

   1. In Medialinks, the foreign key field that contains the primary key of a Person, Family, Source, or Repository record is personID, not persfamID,
   2. Medialinks can link to a Place, in which case personID) is a Placename, and
   3. Medialinks has a one-character field linktype whose value (I,F,S,R, or L) indicates which type of record ID the personID field contains (where 'L' means Place')

   7.3 Mediatypes

   The Mediatypes table is a dictionary (a lookup table) that contains a list of logical categories to which a Media Item can be assigned. Each Media Item has exactly one Mediatype, identified by the field mediatypeID, which is a foreign key to the Mediatypes table. Five Mediatypes - Photos, Documents, Headstones, Histories, Recordings, and Video - are hard-coded in TNG, and do not have entries in the Mediatypes table. Other Mediatypes can be created by a TNG administrator, and are stored in the Mediatypes table.

   TNG Mediatypes are often referred to as "Collections". Each Collection of Media Items - that is to say, the media files themselves, can be stored in a separate folder on the TNG server. The folder names for the built-in folders can be defined through the TNG administrative settings. The folder names for any added mediatypes are defined in the Mediatypes table.

   The Mediatypes table consists of these fields:

   • mediatypeID - The identity key; used as a foreign key by the Media table.
   • display - The Collection name; 'Censuses', 'Houses', etc.; usually capitalized.
   • path - The filepath from the TNG home folder to the folder the Collection is stored in. On most sites, this is just a folder name that matches the collection name, but is not capitalized. On other sites, the media folders are collected below a common subfolder, so that common folder name would also be specified here.
   • liketype - There is some hard-coded special processing for Headstones. This field essentially specifies "Headstones" to cause the collection to be treated like Headstones, or any other other TNG mediatypes for normal handling.
   • icon - The path and filename from the TNG img subfolder to a small image file that displayed next to the collection name in two places:
     • The Media drop-dowm menu on most end-user TNG pages, and
     • The page heading of browsemedia.php when it is focusing on this collection.
     • thumb - The path and filename from the main TNG folder to an image file use in place of an image thumnail for media items in this collection with no thumbnail.
     • exportas - The value to be placed in _TYPE tags within a Media record in a Gedcom output.
     • disabled - A flag that indicates that this collection is not to be assigned to any media items.
     • ordernum - A numeric value that controls the order in Collections are display on the Person Profile page.
     • localpath - The path from the device root to the collection folder.

   7.4 Albums

   Albums are simply a collection of Media Items that can be assigned to a Person, Family, Source, Repository, or Place, just like a single Media Item. Albums are a TNG concept; there is nothing comparable in Gedcom. Albums have these fields:

   • albumID - The identity key, which is used as a foreign key.
   • albumname - A 100-character text field that should be unique, but is not used as a key.
   • description - An arbitrarily long description of the Album.
   • alwayson - A 0/1 flag that indicates whether an Album tied can be displayed even if contains photos that are linked to living people.
   • keywords - An arbitrarily long text field that can be populated with keywords that are used to search for Albums.
   • active - A 0/1 flag that indicates whether an Album can be linked to objects.

   7.5 Albumlinks

   This table (unlike Medialinks) simply determines which Media Items are in an Album. Its fields are

   • albumlinkID - The identity key, which is never used.
   • albumID - A foreign key to an Album.
   • mediaID - A foreign key to a Media Item.
   • ordernum - A number that sequences the Media Items in an Album..
   • defphoto - A mediaID value that links to the Media Item that, in essense, provides the cover photo for the Album. It should refer to an Media Item that is an image, though not necessarily to a Media Item in the photos collection.

   7.6 Album2Entities (or Albumplinks)

   This table (like Medialinks) associates an Albums with a Person, Family, Source, Repository, or Place. Its fields are

   • alinkID - The identity key, which is never used.
   • gedcom - The Tree of the Person, Family, Source, Repository, or Place
   • albumID - A foreign key to an Album.
   • entityID - The partial foreign key value that, when combined with gedcom, identifies a specific Person, Family, Source, Repository, or Place
   • linktype - A single capital letter that identifies the record type of the object that entityID. Its values may be 'I','F','S','R', or 'L', where 'L' is for Places.
   • ordernum - A real number that sequences the objects that the Album links to.

8. Addresses

   The Addresses table, with the primary identity key addresssID contains fields for a street address, an email address, and a phone number. It also contains a gedcom field, but I cannot explain why. The application does not appear to link addresses to the trees.

   There are addresses in the Trees and Users tables; those tables do not use the Addresses table.

9. DNAtests and DNAlinks

   DNA Tests were introduced with TNGv11. They cannot be imported from or exported to Gedcom.

   The DNAtest table describes the results of a DNA test, and the DNAlinks table identifies which People the DNA Test applies to.

10. Users

    This is the security table, defining users and their passwords, rights, and restrictions. It is not genealogical data, but a User can be linked to a Person, a Branch, and a Tree. Some important fields in the Users table are:

    • userID is the identity key.
    • username is also a unique key, and is used as a foreign key value in several tables that identify the User who editied a record.
    • gedcom is, as always, a foreign key to the Trees table, and indicates that this user is restricted to that tree. (If there is a branch, too, that Branch must be within this tree.)
    • branch is a foreign key to the Branches table, and indicates that the user is restricted to that branch.
    • The composite foreign key (mygedcom, personID is a link to a Person (in the Genealogy tables), associated with this user.
    • languageID is a foreign key to the Languages table, and represents the Language that this user has chosen.
    • personID is a link to a Person (in the People tables) that is associated with this user.
    This link may be to a genealogy record for the User, or perhaps just to an ancestor of the User.

11. MostWanted

    This table supports the TNG "Most Wanted" feature, which displays a list of People and/or Media Items about which the site administrator wants more information. Records are set up and edited through TNG Administrative menus at TNG Admin >> Miscellaneous >> Most Wanted. They are displayed by the "Most Wanted" menu entry in the 'Information" drop-down menu on the main menu bar of most end-user pages. The MostWanted table consists of these fields:

    • ID – The identity key.
    • gedcom – The Tree to which the selected Person or Media Item belongs.
    • mwtype – 'person' or 'photo'.
      (As of TNGv11.0.2, the MostWanted feature lets you pick any Media Item, not images, and not just Media Items in the 'Photos' Collection. But they are displayed to users under the heading 'Mystery Photos', and the goal is for the user to recognize a person or place in the photo.)
    • title – A short free-text label for the person or photo.
    • description – An arbitrarily long description of the person or photo, or of what information is being sought.
    • personID – A personID value, which, for mwtype='person', is combined with gedcom to form a foreign key to the People table.
    • mediaID – A mediaID value. This is the primary key of the Media table, so it does not need to be combined with gedcom to find the Media record, but the Media Item's gedcomvalue is always saved in the MostWanted record.

12. Languages

    TNG is "Internationalized" in that it contains a provision for help files and text strings with web pages to be translated into languages other than English. The core application has been translated into over 15 languages. The language support is provided through files that are stored in language folders underneath the main TNG folder. Each TNG site administrator determines which languages are relevant for the users of that site, and uses the Languages table to set up a drop-down list from which users can select a language.

    The Languages table consists of these fields:

    • languageID - the identity key, and is used by any foreign key to this table.
    • display - the name of the language, which can be 100 characters long - plenty of room to express the name of the language in more than one language.
    • folder - the subfolder (below the Languages subfolder within the main TNG folder on the web server) that holds the files of language strings for this language.
    • charset - the character set, such as "ISO-8859-1" or "UTF-8" that will be used in all TNG pages when this Language is selected.
    • norels - A flag that means "No Relationship Display". The norels value of the currently-selected language is adopted as a TNG setting that suppresses the textual relationship description in the "Relationship" program (relationship.php - one of the tabs in the Person Profile). The point of this flag is that some languages express extended relationships such as "great, great, great grandparent" or "great uncle" in ways that are not compatible with the algorithm that calculates those relationships in relationship.php.

13. Reports

    The Reports table implements a two-pronged custom-report-writing system. Only TNG Administrators can create these custom reports, and they can identify which reports can be run be users, and which are reserved for administrators.

    The first "prong" of TNG's reporting system is the "Report Builder" - a web form through which a user can use harcoded lists of composite and atomic Person and Marriage values (e.g. "Birth Date", "Birth Year", "Lastname", "Last Name, First Name", "Occupation: Fact") to select search criteria, display fields, and sort fields. When a user runs such a report, the report program pulls that information together, creates a SQL statement on the fly, and executes that SQL statement.

    In the Report Builder, "Fact" is an Event value - stored in the Event's info field, and sometimes called the "detail" or "descriptive text".

    The second "prong" is SQL statements that are executed exactly as they are entered (except for SQL LIMIT clauses that the reporting tool adds to control pagination in the reports).

    Both types of reports can be used to generate simple reports in two formats: HTML tables in Web pages, or downloadable CSV files.

14. TimelineEvents

    This table stores historical events and their dates so that historical events that occured during a person's lifetime can be placed on the Timeline Report, which is available on the Person Profile Tab Menu. This table does not link to any other tables, nor do any other tables link to it.

15. SaveImport

    This table saves the status of a Gedcom Import so that the user may (possibly) pickup where a long import stopped or crashed rather that have to import the file again.

16. Review of Overloaded Foreign Key fields

    By "Overloaded", I mean that a foreign key field can contain key values for different tables, or can even contain values that are not table keys (as with links to Built-In Events).

    • Branchlinks.persfamID
      Can be a personID or a familyID, and is combined with Branchlinks.gedcom to link to a Person or Family.
    • Events.persfamID
      Can be a personID, familyID, or repoID, or the ID of a Children record, and is combined with Events.gedcom to identiy the Person, Family, Repository, or child that the Event belongs to.
    • Events.eventypeID
      • For Custom Events, this is a foreign key to the Eventtypes table.
      • For Built-in Events, this is a Built-in Tag value, which must be handled by PHP logic in combination Events.persfamID and Events.gedcom, which link to the Person, Family, Repostiory, or Children record that the Event belongs to.
      • Notelinks.persfamID
        Can be a personID, familyID, or sourceID, and is combined with Notelinks.gedcom to identiy the Person, Family, or Source that the Note belongs to.
      • Notelinks.eventtypeID and
        Exactly like Events.eventypeID above, except that a Note cannot be linked to a Repository.
      • Citations.persfamID
        Can be a personID or familyID, or the ID of a Children record, and is combined with Citations.gedcom to identiy the Person, Family, or child that the Citations.eventID belongs to.
      • Medialinks.personID and Album2entities.entityID
        Can be a personID, familyID, sourceID, repoID, or placename, and is combined with the gedcom field to identiy the Person, Family, Source, Repository, or Place that the Media Item or Album is linked to. Note that, unlike the various tables that use overloaded persfamID values, the Medialinks and Album2entities tables also have a single-character linktype that indicates which type of value the Medialinks.personID and Album2entities.entityID field is holding. It turns out that the linktype field is redundant, since personID, familyID, sourceID, repoID, and placename are mutually exclusive, but it is a bit easier to check the linktype than to inspect the link field to figure out which table it is linking to.