FYI, the following message is a quick private exchange between Dave Patton and
myself, where Dave had written me privately, apparently in response to a post I
made to this list in February of 2007 with the same subject line; I am
forwarding it in case my contained brief suggestions on how to design a
genealogy database would be useful to others. -- Darren Duncan
dpat...@beavertown.us wrote [on 2010 May 28]:
Darren
You are welcome to post our conversation as you see fit.
Your suggestions are good ones. We get in trouble when we try to put
these relationships (database and personal) inside the proverbial box.
And as you suggested, source information needs to be displayed and
linked according to some set of rules based on authenticity. That
however should not limit folks from contributing what they believe to be
true, given that such information can prove to be extremely valuable.
Thanks for the additional information. It will be quite useful.
Dave
dpat...@beavertown.us wrote [on 2010 May 28]:
Hi Darren
Hello Dave, thanks for writing.
I assume that your email is a follow-up to a thread on the
perl-gedcom@perl.org mailing list from around February of 2007, given
your subject line.
I would also like to send my response there, so please tell me if this
is okay or not.
Are you designing a relational database to display geneological
information on the internet?
Yes I am, but this has been put on hiatus for awhile.
Before I resume the genealogy project, I am currently dealing with a
more fundamental project, which deals with improving relational DBMSs
themselves and our interactions with them.
As announced last week in other Perl lists, I have completed the
specification of a new programming language which is meant to replace
SQL in the long term, and be an intermediary format for translating
SQL dialects in the short term.
See http://muldis.com/Muldis_D.html for information on that.
Around August or September I expect to have a (open source) reference
implementation made so people can actually run Muldis D code like with
a regular programming language, and then I'll work on other components
to deal with SQL translation or using my language as a front for SQL
databases, etc. Hopefully the community will become involved at least
when the reference is done, if not sooner, and they can be involved in
other parts.
Only after this is done, will I be looking back into my genealogy
project, and be implementing it over Muldis D.
I'm building a relational database on a MYSQL server to display
family information on a public website. The primary table, assigning
an ID number and basic identifying data for individuals, also
includes parental ID numbers. I will establish ancestry with that
relationship.
That been said, it would probably be a good idea for me to make any
changes now before I start writing PHP.
Will your table design be proprietary? I pride myself on learning
from others...
Dave Patton
Corvallis, OR.
My design will be open source, but it will realistically be at least a
full year before I start working on it. I recommend you don't wait
for me if you don't want to wait years to start your project.
I only have a few things to say for now about what you should do for
your design. Any best practices you can find elsewhere are probably
acceptable for now. Using a nondescriptive ID number per person, as
you indicated, is a good start.
One thing I can suggest that may be less typical but is what I would
do, is allow storage of multiple alternatives for the same
information, such as who someone's parents are, and also store
metadata for each piece of information you have saying how sure you
are about that or what your sources are.
So, for example, you can reflect that one source says these people are
the parents of this person while another source says that different
people are their parents. And even in the sake of non conflicts,
there are different kinds of parents, such as birth vs adopted vs
other options. And records about marriages should be separate from
those about births, because who someone's married to may not be who
they have children with, etc. Besically, if you can think of any
reason why one kind of information doesn't automatically mean another
kind, then store both explicitly and give separate reasons for each.
Note that valid sources which you should explicitly state are "first
hand experience" and "assumed most likely considering X" and "just
heard it somewhere". This in addition to regular printed/etc records
you might cite. But for everything, say why you believe what you are
writing is true, or what is claiming it to be.
I hope this is helpful to you.
Also, I strongly recommend you ditch MySQL and use Postgres
(PostgreSQL) instead, especially now before you're starting; the
latter is a much more reliable and better quality DBMS, which all the
savvy people prefer over MySQL. Version 8.4.4 is the latest stable
with 9.0 being in beta now.
And PHP? In response to a Perl list? Well, that's your call. And I
consider the choice of DBMS to be a more important choice than that of
front end language anyway. But I strongly recommend you look at the
Catalyst web framework for Perl, which savvy developers use these days.
-- Darren Duncan
