[VPM] First preview delivery for Victoria.pm July 20th talk

Wed Jul 7 17:00:34 CDT 2004

For those who are interested, this is the verbatim "hand out" that I 
wrote on May 20th of 2002, which was sent to the MacTalk editor for 
printing and release in the June MacTalk.  See Peter Scott's VPM 
announcement.

This email is simply a preview, and some details are specific to one 
database product, which uses a GUI that even a non-programmer can 
use.  You should get a rewritten version later this week that is 
specific to Perl and SQLite, with code.

Note that, while a web or GUI interface may be more friendly for some 
people, that will require a lot more code overhead and external 
dependencies than is good for a simple example or the meeting.  So 
the code I will provide will be for a simple command-line program 
instead, runnable in any command shell.

-- Darren Duncan

--------------

How to Make a Relational Database For Genealogy
by Darren Duncan

This tutorial is a bit different from the ones you usually see in 
MacTalk.  It was designed to be a written guide or step-by-step note 
sheet describing what I will be doing for my June meeting 
presentation.  At that meeting I will demonstrate how to make a 
relational database with FileMaker Pro, with the example being a 
Genealogy database.  I will start at the very beginning, creating the 
files from scratch, until we have a working example over about half 
an hour.  But since there are a lot of details to keep track of, it 
seemed best to have them written down in advance, so you can practice 
before the meeting if you want, and so you don't miss anything during 
the meeting.

While we start at the beginning, this article is more advanced in 
that it doesn't say how to do all the little details.  It doesn't say 
how to make a database file or a database field or a relationship or 
a value list or how to use a "Portal".  So if you don't want to wait 
for the meeting, where I will demonstrate how to do each of these 
things, I recommend that you look at my previous 3-part article on 
FileMaker Pro (MARNIE, PLACE ISSUE DATES HERE).  Of course, the 
meeting is a good time to ask any questions you have.

There are many ways to make a Genealogy database, some being quite 
simple, and others being rather complicated.  Today's example is 
closer to the simple side, but it still has a decent amount of 
flexability designed to save you from having to enter the same data 
more than once.  Duplicated data is the bane of any database, where 
if you ever have to change it, you have to make sure you get to every 
copy.

We will make 6 database files:
	1. Person - has specific core details about a single person.
	2. Person_Detail - lets you add any number of extra details, 
as needed, that can be different for each person; saves your screen 
from being cluttered with miscellaneous database fields that aren't 
used very often.
	3. Marriage - has core details about two people being married.
	4. Source - has specific core details about a single 
information source, which can be either a person or a recorded 
material like a birth certificate or a biography.
	5. Person_Source - what Source materials you cite as 
testimony for the assertions you wrote in Person or Person_Detail.
	6. Marriage_Source - what Source materials you cite as 
testimony for the assertions you wrote in Marriage.

Conceptual Relationships between the 6 files (tables) are:
	[Person] many to one [Person] (Father)
	[Person] many to one [Person] (Mother)
	[Person_Detail] many to one [Person] (Person_Detail)
	[Marriage] many to one [Person] (Husband)
	[Marriage] many to one [Person] (Wife)
	[Person] many to many [Source] (Person_Source; via [Person_Source])
	[Marriage] many to many [Source] (Marriage_Source; via 
[Marriage_Source])

Each database file has at least one form (layout) built into it which 
you use to edit records that are stored in that file.  Through the 
use of Portals, FileMaker Pro lets you also edit records that are 
stored in other, related files.  Today's example database makes use 
of Portals such that you only need to have these 3 files open in 
order to view or edit any records: Person, Marriage, Source.

We probably do most of our work in the Person file.  There, we can 
edit: Person, Person_Detail, Marriage, Person_Source (part of cited 
Source is visible also).  We open the Marriage file mainly when we 
want to edit or view Marriage_Source (part of cited Source is visible 
also).  We open the Source file mainly to view or add details on a 
Source, most of which are only visible here.

Below are all the steps to follow to make the example Genealogy 
database.  There are 4 main parts.  First we create the 6 files and 
define their fields.  Second we define relationships between each 
file as necessary.  (Note that FileMaker Pro has you define the same 
two-file relationship separately in each file that you will refer to 
it in, but here that is usually once.)  Third, we define a few more 
fields, which are simple calculated fields that look up values in 
other files.  Fourth, we will arrange our forms (layouts) as 
appropriate, and create the Portals that go in them.

Note that the attributes beside the fields below are designed to look 
like they would in the list of fields, but the dialog box where you 
set said attributes may have different words to mean the same things.

-- Start --

Create file "Person".

Add fields to "Person":
	Person_ID - Number - Auto-enter Serial, Can't Modify Auto, 
Strict, Required, Unique
	Alternate_ID - Text - Unique
	Name - Text - Required Value
	Sex - Text - Required, By Value List "vl_Sex" ["<unknown>", 
"Male", "Female"]
	Father_ID - Number
	Mother_ID - Number
	Birth_Order - Number
	Birth_Date - Text
	Birth_Place - Text
	Death_Date - Text
	Death_Place - Text
	Burial_Date - Text
	Burial_Place - Text
	Comments - Text

Create file "Person_Detail".

Add fields to "Person_Detail":
	Person_Detail_ID - Number - Auto-enter Serial, Can't Modify 
Auto, Strict, Required, Unique
	Person_ID - Number - Required
	Detail_Name - Text - Required
	Detail_Value - Text

Create file "Marriage".

Add fields to "Marriage":
	Marriage_ID - Number - Auto-enter Serial, Can't Modify Auto, 
Strict, Required, Unique
	Husband_ID - Number
	Wife_ID - Number
	Marriage_Date - Text
	Marriage_Place - Text

Create file "Source".

Add fields to "Source":
	Source_ID - Number - Auto-enter Serial, Can't Modify Auto, 
Strict, Required, Unique
	Name - Text - Required, Unique
	Title - Text
	Date - Text
	Place - Text
	Summary - Text
	Comments - Text

Create file "Person_Source".

Add fields to "Person_Source":
	Person_ID - Number - Required
	Source_ID - Number - Required
	Cited_Portion - Text

Create file "Marriage_Source".

Add fields to "Marriage_Source":
	Marriage_ID - Number - Required
	Source_ID - Number - Required
	Cited_Portion - Text

Add relationships to "Person":
	Father - field "Father_ID" related to "Person"->"Person_ID"
	Mother - field "Mother_ID" related to "Person"->"Person_ID"
	Husband - field "Person_ID" related to 
"Marriage"->"Husband_ID" with: "allow creation of related" and "also 
delete related records" checked
	Wife - field "Person_ID" related to "Marriage"->"Wife_ID" 
with: "allow creation of related" and "also delete related records" 
checked
	Person_Detail - field "Person_ID" related to 
"Person_Detail"->"Person_ID" with: "allow creation of related" and 
"also delete related records" checked
	Person_Source - field "Person_ID" related to 
"Person_Source"->"Person_ID" with: "allow creation of related" and 
"also delete related records" checked

Add relationships to "Marriage":
	Husband - field "Husband_ID" related to "Person"->"Person_ID"
	Wife - field "Wife_ID" related to "Person"->"Person_ID"
	Marriage_Source - field "Marriage_ID" related to 
"Marriage_Source"->"Marriage_ID" with allow add, delete related

Add relationships to "Person_Source":
	Source - field "Source_ID" related to "Source"->"Source_ID"

Add relationships to "Marriage_Source":
	Source - field "Source_ID" related to "Source"->"Source_ID"

Add fields to "Person":
	Father_Name - Calculation - = Father::Name
	Mother_Name - Calculation - = Mother::Name

Add fields to "Marriage":
	Husband_Name - Calculation - = Husband::Name
	Wife_Name - Calculation - = Wife::Name

Add fields to "Person_Source":
	Source_Name - Calculation - = Source::Name

Add fields to "Marriage_Source":
	Source_Name - Calculation - = Source::Name

Arrange Layout in "Person":
	- Arrange all fields (including calculated) in "Person" as is suitable.
		- Format field "Sex" as Pop-up list using value list "vl_Sex"
	- Add Portal on "Person_Detail" with fields: Detail_Name, Detail_Value
	- Add Portal on "Husband" with fields: Wife_ID, Wife_Name, 
Marriage_Date, Marriage_Place
	- Add Portal on "Wife" with fields: Husband_ID, Husband_Name, 
Marriage_Date, Marriage_Place
	- Add Portal on "Person_Source" with fields: Source_ID, 
Source_Name, Cited_Portion

Arrange Layout in "Marriage":
	- Arrange all fields (including calculated) in "Marriage" as 
is suitable.
	- Add Portal on "Marriage_Source" with fields: Source_ID, 
Source_Name, Cited_Portion

Arrange Layout in "Source":
	- Arrange all fields (including calculated) in "Source" as is suitable.

-- End --