CODEWITHSUNDEEP
phpdatabasecakephp
Kpaekn
Kpaekn

Reputation: 339

CakePHP Many to One - single table

I have a Members table: Members(id, name, gender, head_id)

What I want is to make family relationships based around the head of household (who is a member). So its like: One member belongs to one Family (defined by a member)

Maybe I could split it into 2 tables, a Members and Families table: Families(id, head_id) and the Members table would have a family_id instead of head_id.

The main problem would be on adding new members and modifying the relationships.

EDIT: All the answers were great.
I ended up doing it manually. Dave's solution is what I was looking for, but didn't workout exactly the way I was hoping.

Upvotes: 1

Views: 942

Answers (3)

BadHorsie
BadHorsie

Reputation: 14544

You should have two tables. That is the best way, and will offer you the most flexibility and ease of use within your application.

Database Tables

// families

id | name
------------------------------
 1 | Smith
 2 | Jones
 3 | Davis  


// members table

id | family_id | name      | gender
-----------------------------------
 1 |     2     | James     |   M
 2 |     3     | Christine |   F
 3 |     1     | David     |   M
 4 |     2     | Mark      |   M
 5 |     1     | Simon     |   M
 6 |     1     | Lucy      |   F

CakePHP Models

Then you just need to define your models so they have the correct relationships.

// app/Model/Family.php
class Family extends AppModel {

    public $hasMany = array('Member');
}

// app/Model/Member.php
class Member extends AppModel {

    public $belongsTo = array('Family');
}

Then you can retrieve your families like this:

CakePHP Controller

// Find all members that belong to Family 1
$family = $this->Member->find('all', array(
    'conditions' => array('family_id' => 1)
));

OR

// Find Family 1 and get all its members
$family = $this->Family->find('first', array(
    'conditions' => array('family_id' => 1),
    'contain' => array('Member')
));

You shouldn't have any problems with adding new members or modifying the relationships, like you are worried about, but if you run into any specific problems we can likely help you. This kind of model relationship is extremely common.

Upvotes: 0

Alvaro
Alvaro

Reputation: 41595

From a Database point of view, I consider you should have 3 tables:

  • persons (id, passport_number, name, dob, ...)
  • families (id, father_id, mother_id, surname, number_of_members,... )
  • families_persons (family_id, person_id)

A family is defined as the union of two persons and might have some other common fields such as surname.

Anyway, if you do it in your way, you can do it with one only table. (father with head_id set to 0, and the rest of the family members with head_id referring to his id.

In case you want to use two tables, controllers can use more than one Model, so it is not a problem to deal with more table in the save action.

Upvotes: 1

Dave
Dave

Reputation: 29121

See "Multiple Relations to the Same Model"

"It is also possible to create self associations as shown below:"

class Post extends AppModel {

    public $belongsTo = array(
        'Parent' => array(
            'className' => 'Post',
            'foreignKey' => 'parent_id'
        )
    );

    public $hasMany = array(
        'Children' => array(
            'className' => 'Post',
            'foreignKey' => 'parent_id'
        )
    );
}

Upvotes: 1

Related Questions