The other key table, Source, is important because, in an ideal world, every piece of information should be documented as to where the information came from. Now, in the real world, this won't happen, especially due to the tedious nature of documenting research, however, the functionality is there if the researcher wants (or feels the need) to use it.
From these two base tables, the database takes steroids and breaks out into no less than 24 distinct tables, which allows the greatest amount of flexibility for the researcher.
The Surety field in the Name table, like in all the other tables that have a Surety field, represents the level of surety that this record contains the actual fact. This level of surety can be on any integer scale (i.e. -3 to +3, 0 to 10, etc.) with one end of the spectrum designated as most sure (definite fact) and the other end as least sure (wild speculation, and probably false). So, if the user chooses a range of -3 to +3 with +3 representing fact and -3 representing fiction, a user would put +3 for resources of irrefutable surety (i.e. the researcher is entering his or her own name). Family legends might start at the other end of the spectrum until proven by credible and reliable resources.
The Primary field in the Name table is also an idea that flows through to other tables. While a person can have many names throughout a lifetime, but the name marked as Primary is the name that will appear in spaces where only one name will fit (i.e. on a Pedigree Chart). Looking at recent history makes it apparent why we need to keep names in a separate table, rather than as a field in the Person table. For example, a woman born as Anne Smith in 1952 may have changed her name during the "Summer of Love" to something like Butterfly Moon. She may have reared a child under this name as well, which means that Butterfly Moon would appear on the child's birth certificate as the mother's name. Later, when she married in 1983, she may have changed her name again to take her new husband's last name, perhaps becoming Butterfly Jacobsen. So in this example, we have a person who is listed on legal documents under three names, which means that the researcher needs to keep track of three names. However, one of these names needs to be designated as the primary name. In most cases, this will be the person's birth name, but the researcher may decide to use any of the names that have been discovered as the primary. It's all a matter of how the researcher wants the person listed on the reports.
Since each name can be referenced in many resources, we include a table to store this information as well in the Name-source table. Its behavior is the same as that of the Event-source table explained below.
Putting the events in a separate table, we enable the researcher to track each of these different events so that the primary event (much like the primary name) can be easily switched to another record of the same type. Events in the Event table are categorized by an event type, which defines the text that will appear both on the screen and in reports as well as the number of main participants in the event - a graduation, for example, will have one main participant, while a marriage will have two main participants.
A birth, although it really involves at least three people, is categorized here as having only one main participant, the person who is being born. The reasoning here is that as we research, we will most often find all of the information for any particular person in pieces. We will find a person's name and gender in one resource, birth date in a second resource, a list of siblings in a third, and maybe a census record in a fourth. We may never find the person's parents in our research. Furthermore, when we look at the way that we associate birth dates with people, we don't usually write that two people had a child on such-and-such a date; we usually write that so-and-so was born on a certain date. Then, after writing this, we usually write the names of the person's parents in another location entirely. The parental relationships in this database are tracked in the Family and Child tables.
Additionally, every event can be witnessed by one or more other people in our database. For example, at a formal wedding ceremony, both the bride's and groom's families normally attend. Having this information in the database can help us track where a person was and when a person lived (or died) more efficiently. If we know that cousin Joe attended Sarah's wedding, we know that he didn't die until after that date, and was born before that date. This information is stored in the Witness table. This table contains merely the Event ID and the Person ID of the witness, because all of the person's information is stored elsewhere while all of the event's information is stored in the Event table. However, we don't establish a Witness-source table because we use the Event-source table to link the event to the resource where we found the information.
Since every event in a person's life can be referenced in a myriad of resources, we have a table that links events to resources - Event-source. The general information about the resource is stored in the Source table, and the Event-source table includes the foreign keys from both Event and Source to create the link between the two objects. Also, in Event-source, is any information that is specific to where in the resource the information can be obtained (i.e. page numbers, the surety level for this event, any additional detail like the film reel or volume number, and freeform notes).
We might also find, in another example, that great-grandfather was enlisted in the Army. If we don't have the muster rolls or know of any battles in which he participated, then we don't have an event we can use to track this military information. We may find it helpful the next time we go to the local library to have a list of people in our database who did serve in one of the armed forces so that we then have the ability to find the muster rolls and battles.
It is information like this that the Attributes are designed for. Each attribute in the database has a type and a value. The type identifies what information is being stored for the attribute while the value is the data itself. To continue with the two examples above, the woman who died unmarried and without issue might have a NeverMarried attribute set to Y and a WithoutIssue attribute also set to Y. The great-grandfather who served in the army might have a Military attribute set to A to denote Army service.
Attributes, like events can be referenced in a multitude of resources, so we include the Attribute-source table to record this information. This table behaves the same way as the Event-source table explained above.
Since every family has anywhere from zero to 15 or more children (although this may seem a bit extreme by today's standards...), not counting adoptions or foster children, we list the child relationships in the Child table. The fields here are also rather simple, foreign keys for each of the Family ID and Person ID, a freeform note field, and a character to identify the type of relationship to this family (i.e. S or D for Son or Daughter, A for Adopted, T for Step, etc.).
Since individual resources can have information on families and children, there is a table that links the Child records to resources (Child-source) and a table that links the Family records to resources (Family-source). These two tables behave just like the Event-source table mentioned above.
Often, in doing our research, we will find information that is published by someone who is in our database. For this case, we also include the Person ID field in the Source table. By storing the Person ID of the author, if it's available, we save memory and time because we don't have to retype the name information here.
Working our way into more general terms about places, every place is, usually, part of a city or town somewhere. Generalizing the locations further, every place is within a county or parish, every county is within a state or province, every state is within a country and every country is within a continent. Since political boundaries change throughout time (as in the reunification of Germany, or the breakup of the Soviet Union), we allow the user to enter the place information exactly as it is written in the resource, but we save the names in these other tables so the user doesn't have to always retype the same names over and over again. While the user is entering data, we programmatically examine what has been entered into a field, and compare it with the keys in the appropriate region table, then prompt the user if this is the correct location.
Note: since not everyone will want these prompts popping up all the time, we provide a global option for the user whether or not to lookup this information as it is being typed. Regardless of the option that is chosen, the user can type in the full location as it appears in the resource.
The Researcher table in this database can be thought of as a miniature phone book. The database compiler's information will be stored as researcher ID number 1, while other researchers' information will be stored as other records in the table. In addition to all the usual fields in a contact list, we also include a field for the surname list that the researcher is interested in. This way, we can easily pick out the right person to correspond with for more research.
In doing our research, we don't want to waste time studying the same resources more than a couple times for specific information, so we include a research log. The Research-log table is mainly made up of foreign keys from each of the other types of objects that describe the people in the database: Person, Name, Attribute, Event and Family; it also includes foreign keys for the resources and locations where the research has taken place: Source, Researcher, Repository and Place.
Note that for any record in the Research Log, each of these foreign keys is optional. The research that we do takes place on specific dates, so we record the date here as well, just like a date in the rest of the database. We also include notes and detail fields for the user to fill in information about what has been researched and what still needs to be examined.
Resources can be stored in multiple locations as well. We can create a table (maybe called Source-repository) that links the resources to the repositories. However, it wasn't done here because typically when we find a resource, we always go back to the same place to examine that resource. Right now, if we find the same resource in multiple locations, creating a second or third (or however many) Source record will be the way to store this information. Besides, most of the researchers that I've talked to are not researching professionally and don't need quite as extensive a level of detail as this.
Attribute Field Name Field Type Keys atr_aty_id long integer foreign key from Attribute-type atr_id long integer primary key atr_per_id long integer foreign key from Person atr_primary bit(1) atr_surety integer atr_value character(1)
Attribute-source Field Name Field Type Keys atr-sou_atr_id long integer primary key (foreign key from Attribute) atr-sou_detail memo atr-sou_note memo atr-sou_pagelist memo atr-sou_sou_id long integer primary key (foreign key from Source) atr-sou_surety integer
Attribute-type Field Name Field Type Keys aty_id long integer primary key aty_text character(25) aty_validvalues character(50)
Child Field Name Field Type Keys chi_fam_id long integer foreign key from Family chi_id long integer primary key chi_note memo chi_per_id long integer foreign key from Person chi_relationship character(1)
City Field Name Field Type Keys cit_abbr5 character(5) cit_country memo cit_county memo cit_id long integer primary key cit_name memo cit_state memo
Continent Field Name Field Type Keys cnt_abbr3 character(3) cnt_id long integer primary key cnt_name character(10)
Country Field Name Field Type Keys ctr_abbr2 character(2) ctr_abbr3 character(3) ctr_id long integer primary key ctr_name memo
County Field Name Field Type Keys cty_abbr character(5) cty_country memo cty_id long integer primary key cty_name memo cty_state memo
Event Field Name Field Type Keys evt_calendar integer evt_day integer evt_detail memo evt_id long integer primary key evt_month integer evt_per_id1 long integer foreign key from Person evt_per_id2 long integer foreign key from Person evt_plc_id long integer foreign key from Place evt_primary bit(1) evt_surety integer evt_ety_id long integer foreign key from Event-type evt_year long integer
Event-source Field Name Field Type Keys evt-sou_detail memo evt-sou_evt_id long integer primary key (foreign key from Event) evt-sou_note memo evt-sou_pagelist memo evt-sou_sou_id long integer primary key (foreign key from Source) evt-sou_surety integer
Event-type Field Name Field Type Keys ety_abbr character(1) ety_id long integer primary key ety_numperson bit(1) ety_sentence memo ety_shorttext character(5) ety_text character(25)
Family Field Name Field Type Keys fam_father_per_id long integer foreign key from Person fam_id long integer primary key fam_mother_per_id long integer foreign key from Person fam_note memo
Family-source Field Name Field Type Keys fam-sou_detail memo fam-sou_fam_id long integer primary key (foreign key from Family) fam-sou_note memo fam-sou_pagelist memo fam-sou_sou_id long integer primary key (foreign key from Source) fam-sou_surety integer
Name Field Name Field Type Keys nam_calendar integer nam_day integer nam_given memo nam_id long integer primary key nam_month integer nam_note memo nam_per_id long integer foreign key from Person nam_postfix memo nam_prefix memo nam_primary bit(1) nam_surety integer nam_surname memo nam_year long integer
Name-source Field Name Field Type Keys nam-sou_detail memo nam-sou_nam_id long integer primary key (foreign key from Name) nam-sou_note memo nam-sou_pagelist memo nam-sou_sou_id long integer primary key (foreign key from Source) nam-sou_surety integer
Person Field Name Field Type Keys per_id long integer primary key
Place Field Name Field Type Keys plc_address1 memo plc_address2 memo plc_city memo plc_country memo plc_county memo plc_description memo plc_id long integer primary key plc_name memo plc_notes memo plc_state memo
Repository Field Name Field Type Keys rep_id long integer primary key rep_name memo rep_notes memo rep_plc_id long integer foreign key from Place
Researcher Field Name Field Type Keys res_email memo res_fax memo res_id long integer primary key res_name memo res_note memo res_per_id long integer foreign key from Person res_phone memo res_plc_id long integer foreign key from Place res_preferredcontact character(1) res_primary bit(1) res_surnamelist memo
Research-log Field Name Field Type Keys rlg_atr_id long integer foreign key from Attribute rlg_calendar integer rlg_day integer rlg_detail memo rlg_evt_id long integer foreign key from Event rlg_fam_id long integer foreign key from Family rlg_id long integer primary key rlg_month integer rlg_nam_id long integer foreign key from Name rlg_note memo rlg_per_id long integer foreign key from Person rlg_plc_id long integer foreign key from Place rlg_rep_id long integer foreign key from Repository rlg_res_id long integer foreign key from Researcher rlg_sou_id long integer foreign key from Source rlg_year long integer
Source Field Name Field Type Keys sou_author memo sou_author_per_id long integer foreign key from Person sou_catalognumber memo sou_copyright memo sou_description memo sou_id long integer primary key sou_isbn character(13) sou_name memo sou_plc_id long integer foreign key from Place sou_publisher memo sou_publishlocation memo sou_rep_id long integer foreign key from Repository sou_replocation memo sou_surnamelist memo
State Field Name Field Type Keys stt_abbr character(5) stt_abbr2 character(2) stt_country memo stt_id long integer primary key stt_name memo
+--------+ 1 M +------+
| Person |-----------------| Name |
+--------+ +------+
|1 |1 |1
| | | M +-------+ M 1 +------------+
| | +------------------| Event |-----------| Event-type |
| | +-------+ +------------+
| | M +---------+ M |1
| +------| Witness |-----+
| +---------+
| M +-----------+ M 1 +----------------+
+------------------------| Attribute |-------| Attribute-type |
+-----------+ +----------------+
+--------+ 1 (Parent) M +--------+
| Person |------------------| Family |
+--------+ +--------+
|1 |1
| M +-------+ M |
+-------| Child |----+
+-------+
+------+ 1 M +-------------+ M 1 +--------+
| Name |-------------| Name-source |----------| Source |
+------+ +-------------+ +--------+
|1 |1 |1
+-------+ 1 M +--------------+ M | | |
| Event |------------| Event-source |----------+ | |
+-------+ +--------------+ | |
| |
+-----------+ 1 M +------------------+ M | |
| Attribute |--------| Attribute-source |---------+ |
+-----------+ +------------------+ |
|
+--------+ 1 M +---------------+ M |
| Family |-----------| Family-source |---------------+
+--------+ +---------------+
+--------+ M 1 +------------+ M 1 +-------+
| Source |-----------| Repository |----------| Place |
+--------+ +------------+ +-------+
|1 |1
+-------+ M | |
| Event |-------------------------------------+ |
+-------+ |
|
+--------------+ M |
| Research-log |---------------------------------+
+--------------+
+------------+ 1 M +--------------+
| Researcher |---------------------------| Research-log |
+------------+ +--------------+
|M |M |M |M |M |M
|1 | | | | |
+--------+ 1 | | | | |
| Person |--------------------------------+ | | | |
+--------+ | | | |
| | | |
+-----------+ 1 | | | |
| Attribute |--------------------------------+ | | |
+-----------+ | | |
| | |
+------+ 1 | | |
| Name |----------------------------------------+ | |
+------+ | |
| |
+-------+ 1 | |
| Event |------------------------------------------+ |
+-------+ |
|
+--------+ 1 |
| Family |--------------------------------------------+
+--------+
+-------+ 1 M +--------------+
| Place |-------------------------------| Research-log |
+-------+ +--------------+
|M |M |M
+------------+ 1 | | |
| Repository |---------------------------+ | |
+------------+ | |
| |
+------------+ 1 | |
| Researcher |------------------------------+ |
+------------+ |
|
+--------+ 1 |
| Source |-------------------------------------+
+--------+
+------+
| City | ---\
+------+ \
\
+--------+ \
| County | -------\
+--------+ \
\
+-------+ \
| State | -----------> These tables are not relationally linked to others or
+-------+ / to each other due to the nature of the research. We
/ won't necessarily have all of the location information
+---------+ / at once, and political boundaries change names and
| Country | -------/ locations over time. They are included to save typing
+---------+ / time and prevent errors.
/
+-----------+ /
| Continent | ---/
+-----------+