Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: Questions on genealogy schema

    I'm putting together a genealogy site for my extended family using MySQL and PHP, and am beginning with establishing a db schema. Was hoping some folks would be willing to take a look and advise me, as I'm not a database pro though have been getting comfortable with MySQL lately. But the relational aspect of schemas is still a little bit of a mystery to me.

    I posted a visual representation of the schema here:http://edgeis.com/graphics/projects/database-schema.png

    I'll try and attach a PDF also.
    Attached Files Attached Files
    Last edited by mike_bike_kite; 09-22-10 at 03:07. Reason: improved thread title

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    I suggest use of mapping tables for representing many-to-many relations.
    Cheers....

    baburajv

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Can you give me an example using my schema?

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    assuming that the relatives and events entities have many to many relationship

    ( one relative participate in many events and one event has many relatives as participants),

    i suggest use of a third table

    "eventparticipants" with two columns

    eventid (referring to events table PK)
    relativeid (referring to relatives table PK)

    NOTE: i assume (looking at the column "participants" in events table) that you are representing multipleparticipants for an event in this column. correct me if my understanding is wrong.
    Cheers....

    baburajv

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Is there a reason for this thread to be called "bistif 1888-3"? The aim of forums is to not only to provide you with an answer to your particular problem but also to allow others to search for solutions to their own problems. Having threads titled like this stops this process.

  6. #6
    Join Date
    Sep 2010
    Posts
    3
    babura,

    Thanks--you're correct, there would be multiple participants in an event, and they would be identified by the "relative_id" number referenced in the "relatives" table.

    So you think I should have a number of additional "mapping tables"?

    What is the advantage of that? Not doubting your advice, just want to understand the concept behind it.

    Mike,

    I accidentally entered the captcha text into the title (not used to having captcha on forums). Tried to go back and edit it but it won't let me. Maybe a moderator would do so?

    So do you have any feedback on the schema?

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Megunticook View Post
    So do you have any feedback on the schema?
    I just typed genealogy into the search facility and it came up with loads of discussions and database designs. I'd just read through the various discussions to see what's important to you. If it's just for your family then personally I'd use an existing site and enter the info you want - it seems a lot of effort to produce a system just for one family, especially if that system is available already.

    With your own design I'd change the various media tables (Audio,Video,Picture etc) and put them all into one table with a type field to distinguish the different formats etc. I didn't really put much effort into examining the design as I feel you should look at available systems first.

    I'll look into changing the thread title for you - even as a moderator I can't see an obvious link.

    Mike

  8. #8
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    By having a mapping table ("eventparticipant"), we avoid the repetition of "event" information for each of the participant. Below is an example where an event has multiple participants.
    Based on the current design, the data will be as follows

    table: "relatives"
    relativeId, FName, LName ....
    1 A B
    2 C D
    3 E F

    table "events"
    eventid type participants date location_town location_state country
    100 Type1 1 2010-Sep-01 XYZ PQR US
    100 Type1 2 2010-Sep-01 XYZ PQR US
    100 Type1 3 2010-Sep-01 XYZ PQR US

    with the current schema, the event information will be repeated for each participant.

    By having 3 table (relatives, events and eventparticipant), we avoid this unwanted repetition of information. Please see below

    table: "relatives"
    relativeId, FName, LName ....
    1 A B
    2 C D
    3 E F

    table "events"
    eventid type date location_town location_state country
    100 Type1 2010-Sep-01 XYZ PQR US

    table "eventparticipant"
    eventid participantid
    100 1
    100 2
    100 3

    Here, we remove the repetition of information by storing only the required attributes in the mapping table. Hope this clarifies your question on the advantages of having a mapping table.
    Cheers....

    baburajv

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •