Building a Genealogy site with PHP/MysQL (GEDCOM design)

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building a Genealogy site with PHP/MysQL (GEDCOM design)

Post by kendall »

Hello,

I have been undertaken a task to build a family oriented website in which a documented listing of a family tree will be placed online for family members to use.

Now i have experimented with Family TRee Software and i am aware of the GEDCOM syntax. However my development will not be to as a detailed extent to GEDCom.

Im trying to figure out how best to design a database to hold the relative information

so far i have split the information into 3 tables

Individuals - main info

Families - reference ids

Children - reference ids

However i notice that i may run into double information

Has anyone ever encountered tutorials in this aspect?

I have tried searching the net bu to no avail...i'm more interested in the database design aspects of it

Kendall
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

What do you mean by "double information"?

For tutorials google for "Entity-Relation Modelling". For a very short description see this thread.

I'm not sure what you mean the ramilies relation(table) to represent.. Is it showing marriages? It it should "family groupingss? Is it "root nodes of families"?

Erc
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building a Genealogy site with PHP/MysQL (GEDCOM

Post by kendall »

Erc,

I my self am not sure. Not even sure a GEDCOM style migth be what I want in the first place but i've heard so much about it it was worth looking into but for starters...

In the design specs i see i should probably separe the info into the 3 tables

indivuals table-
this table has all the main info in it id, first name, last name, yada yada

the family table-
this table is what will relation the idivduals to the family by terms of id , family id, husband, wife, marreid and divorce dates. Now i was trying to fit children into it but mainly every family had more than one child and every individual is a child

so i say hey make a children table
so children will list all the individuals and what family they are related to referenced by id's
thus i get

table children
--FAMID--CHILDID--

table family
--FAMID--HUS--WIFE--MARRID--DIVORCED

table indivudal
--INDID--FNAME--LNAME--SEX-- etc. etc..

now i realise that maybe i do not need a childrens table however it may seem that it could make searching a bit more easier.

I think my design needs a bit more fine tunnning hence im looking to see by comparison how thing could be improved.

I can use some advice on this

Kendall
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Oh, by the way i read the thread about the database design concept. I'm think im on that level but i just need some second opinons to see if im going ahead good. By the way any one saw any tuts on converting GEDCOM for MySQL use i can use the links
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

OK I think I'm understanding what you're getting at. If your new to PHP and/MySQL I would suggest the following:

relation: people
primary key: peopleid, auto-assigned
birthdate
deathdate
family name
given name
middle name
maiden name
gender


relation: pairings
primary key pairingid, auto-assigned
husband: fk to people
wife: fk to people -- NOTE1 does your schema need to reflect non-traditional pairings?
effective: date
disolved: date

relationship: descendants
primary key, peopleid fk link to people
mother: fk to people -- NOTE2 does you schema need to reflect adoptions?
father fk to people

Note1: if you need to allow non-monogamous relations this gets greatly complicated; if you need to allow same-sex relations you might want to add person_1_role, person_2_role or something??

Note2: if you don't need to allow adoptions, then descendants can link to pairings (assuming no illegitimacy)

If you have more experience with MySQL and PHP, I'ld suggest further normalizing the people table and avoid the use of nulls, by splitting of tables of given name (
people_given_names
peopleid, given_name, name_order)
and a people_maiden_names (peopleid, maiden_name)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building a Genealogy site with PHP/MysQL (GEDCOM design)

Post by kendall »

Eric,

[quote]
Note1: if you need to allow non-monogamous relations this gets greatly complicated; if
you need to allow same-sex relations you might want to add person_1_role,
person_2_role or something??

Note2: if you don't need to allow adoptions, then descendants can link to pairings
(assuming no illegitimacy)
[/quote]

Whoooooooooooooooooooooooa!

I was practically on the same page till you went there!!! let's hope the people i'm making this thing for doesnt reach that far

But i seem to be ketching on (PHP novice MySQL stupified) and i am on the rite track after all

Now i went a bit further i thought id separate the actuall main info and make a realational table i.e.

table individual
---peopleid---parings---descendants---

and have a individual_info
---Fname--Lname--- etc. etc.

reason being i may make the search query strings a bit easier to construct. but how is it in search optimising?

my other reason for the separation is that the site is going to be personalisation oriented and im thinking of using the data information for this thus things like email address personal notes user name passwords will need to be fit in withouth having to create numerous tables.

Kendall
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Hmm I think your "table individual" is bad design. Its highly denormalized, for instance descendants aren't required to enter a pairing, but the table make it so. Also multiple descendants will have to replicate hte pairing info, etc. People who have married/divroced/remarried would have troulbe in that system.

Regarding a individual_info, that makes some sense. For most of my login systems I have a relvar named Users (username, password, email, person) and a relvar People (firstname, lastname, etc) another one for People_addresses, etc My systems have lots of people who don't have logins, so seperately the login from the detailed info makes sense in my case. YMMV
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building a Genealogy site with PHP/MysQL (GEDCOM design)

Post by kendall »

Erc,

Isnt that like a divorce to never workout the way things should. Ok my reasons for it would be because of the output im going to generate...

i will want to generate a profile page of the person with the following attr-

Individual info - name brithday sex etc...
Family info (if any) married, divorced wife kids etc...
then Descendant info - father mother siblings.

now already i see i have to do more than one search query concerning the having to find his kids and his brothers and sisters. I'm thinking...that's way to much double back i.e.

if i find out he;s married i need his kids thus double back to the individual table and find his kids then double back and find his brothers and sisters.

therefore im thinking i need to put a next column in the individual area to try to link to families...so to me that means double info...im trying to find a work around for this which was why i separeted the individuals into a id information table. But do you see where im coming from?

Kendall
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Well there is nothing wrong with using multiple queries. Its much much better than have redundant data in the database.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Building a Genealogy site with PHP/MysQL (GEDCOM design)

Post by kendall »

Erc,

Well i should be thank full that you can't stop being a family member or detele an individual cause she no longer exists else it would have been difficult coding there.

i'm still think theres a way around it though but i have already started inuputting data to start coding and testing let hope i dont blow up anything in the process

will keep you updated

thanks for the advice

Kendall

P.S. (php ametuer mysql stupified)...yep i think it better defines me
dnuttall
Forum Newbie
Posts: 4
Joined: Sat Aug 16, 2003 7:55 am
Location: San Antonio, Texas

Building a genealogy site

Post by dnuttall »

With all due respect for your vigorous intentions, for $25 you can buy and use a fully functional PHP application by Darrin Lythgoe.

I have it running inside of PostNuke at http://www.ourvirtualheritage.com.

It has a couple of problems when you implement it the way I did, but personally I couldn't afford the time/energy compared with $25 to Darrin.
The software is called "The Next Generation" (TNG) and suffice it to say I have no stake in the company or anything except an avid interest in getting an active and robust site for my family's history.

For details/download (for paying customers only), see:
http://lythgoes.net/genealogy/index.html

Good luck and correspond off-line if you want on genealogy issues, ideas, etc.

Dave Nuttall
San Antonio, TX
qartis
Forum Contributor
Posts: 271
Joined: Sat Dec 14, 2002 4:43 pm
Location: BC, Canada
Contact:

Post by qartis »

http://www.mynukegenealogy.de/

Free PHP-Nuke module, sounds like everything you're looking for.
Post Reply