I am still new in programming as this is my 1st year in programming field. I have read few other post about designing family tree in here but i still didn't understand some details so i need your help. by the way, i use PHP
this is my simplified version form
// family leader
<div id="family_leader">
<input type="text" name="name[]">
<input type="text" name="ic[]"> // ic is the same like SSN
</div>
// family member
// here input wife or child info
// i use javascript to clone the div so user can add how many family member they have.
<div id="family_member">
<input type="text" name="name[]">
<input type="number" name="ic[]">
<input type="text" name="relationship[]">
</div>
and i'm thinking of making a database this way
1st table will be like this
User table
+--------+--------------+--------------+
| id | name | ic |
+--------+--------------+--------------+
| id1 | John | 9357906268 |
+--------+--------------+--------------+
| id2 | Josh | 9357222228 |
+--------+--------------+--------------+
and i will have another table for their relationship
Relationship table
the table will look like this
+-------------+--------------+--------------+
| owner_id | family_id | relationship |
+-------------+--------------+--------------+
| ic1/id1 | ic2/id2 | father |
+-------------+--------------+--------------+
| ic2/id2 | ic1/id1 | child |
+-------------+--------------+--------------+
i make it this way in case of single parent and i want to make this data viewable in a profile
its like
husband has 1 wife father has 1 or many child child has 1 father
so far this is what i got
family class
<?php
/**
* this is my family class
*/
class Family
{
function __construct(argument)
{
# database construct here
}
public function getUser($_POST)
{
foreach ($_POST['name'] as $row) {
# this is how im going to insert the data of user into user table
$sql1="INSERT INTO User ( name, ic) values (" $_POST[name][i]", "$_POST[ic][i]")" // e.g John, 9357906268
}
}
public function getRelation($_POST)
{
$sql2="INSERT INTO Relationship ( owner_id, family_id, relationship) values ( $_POST['ic'][i], $_POST['ic'][1], $_POST['relationship'][i])"
/**
* this is where my problem reside
* i don't know how to make this function
*/
}
}
so how do i insert correctly into the 2nd table?
i'm also thinking of doing a switch case inside the getRelation
switch ($_POST['relation']) {
case 'father':
// in here is if Josh is a father to John then John is a child to Josh.
break;
case 'mother':
# code...
break;
// ... more case
default:
# code...
break;
}
or maybe create more function in the class?
public function isFather($value='')
{
# code...
}
public function isMother($value='')
{
#
}
the problem is i am not sure how to do the logic inside the function or switch case to determine the relationship of the users.
and is this the correct way of database design and OOP?
thank you for your time .
and is this the correct way of database design?
Small fixes: define Primary Key in User and use it in Relations.owner_id and Relations.family_id. Add FOREIGN KEY constraints on those 2 columns.
Also maybe you need to store the gender of the user as it may happen the family_leader is actually mother.
Storing Relations.relationship as a string may give performance problem if you have millions of records but for a learning task it is ok. In MySQL there is a CHECK Constraint that can be used to limit the field values to some fixed list of strings e.g. CHECK relationship IN('father', 'son', 'daughter','wife','husband').
Before answering the next question I want to note a minor problem in your HTML code:
this is my simplified version form
You are mixing here the family_leader and family_member. It is better separate them:
<div id="family_leader">
<input type="text" name="family_leader[name]">
<input type="text" name="family_leader[ic]"> // ic is the same like SSN
</div>
<div class="family_member"> <!-- don't use id if you're gonna duplicate this div -->
<input type="text" name="name[]">
<input type="number" name="ic[]">
<input type="text" name="relationship[]"> <!-- consider using <select> tag here to limit possible relationhips -->
</div>
and OOP?
A small note: using getUser and getRelation isn't good. "Get" means you're retrieving data from somewhere without changing. When your function inserts the data you should name it saveUser (and saveRelation).
Now you asked:
the problem is i am not sure how to do the logic inside the function or switch case to determine the relationship of the users.
I would recommend following OOP approach: create the following User class:
<?php
class User {
public $id;
public $name;
public $ic;
public function save() {
// save the user to the db and update $this->id with autogenerated id
}
public function addChild(User $child) {
// add to `Relations`: [$this->id, $child->id, 'father']
// then add to `Relations`: [$child->id, $this->id, 'child']
}
public function addWife(User $wife) {
// check if this user already has a wife and throw an Exception "Wife already exists"
//
// add to `Relations`: [$this->id, $wife->id, 'husband']
// then add to `Relations`: [$wife->id, $this->id, 'wife']
}
public function addRelative($relationship, User $relative) {
switch ($relationship) {
case 'child':
$this->addChild($relative);
break;
case 'wife':
$this->addWife($relative);
break;
default:
throw new Exception("Unknown relationship:" . $relationship);
break;
}
}
}
then in the Family class use following function:
public function saveFamily() {
$family_leader = new User()
$family_leader->name = $_POST['family_leader']['name'];
$family_leader->ic = $_POST['family_leader']['ic'];
$family_leader->save();
foreach ($_POST['name'] as $i => $name) {
$family_member = new User();
$family_member->name = $name;
$family_member->ic = $_POST['ic'][$i];
$family_member->save();
try {
$family_leader->addRelative($_POST['relationship']['$i'], $family_member);
} catch (Exception $e) {
die ('Relationship not added:' . $e->getMessage());
}
}
}