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.

I suggest use of mapping tables for representing many-to-many relations.

Can you give me an example using my schema?

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.

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.

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?

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

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.