tableA HABTM tableA - cakePHP DB Design Question

Question

I have the need to create the functionality to allow my registered users to associate themselves with other uses and create a family. My database tables look something like this:

db.persons - (Bob, Jane, Timmy, etc)

• id
• name
• email

db.persons_persons

• id
• person__id_parent
• person_id_child
• relationtype_id

db.relationtypes - (spouse, child, nephew, etc)

• id
• name

With this structure, I should be able to specify that Bob is a Spouse of Jane in the persons_persons table. The record in db.persons_persons would look something like (psuedo):

ID | person_parent | person_child | relationtype
1  | Bob           | Jane         | Spouse
2  | Bob           | Timmy        | Child

I realize this could cause some data fragmentation since if Bob and Jane are married, then a child of one would have to be associated with the other. However that is not my question. My question is whether or not this database setup is 'most-correct' for use in a cakePHP application.

Answer 1

Firstly, CakePHP will automatigally inflect a people table into a people controller and a person model, so you don't need to call it persons. Then I'd call the lookup table relationships rather than persons_persons.

I'd call the fields in your relationships table person_id, relation_id and relation_type_id, and then add an underscore in your relationtypes table name so it becomes relation_types.

I think this is more semantic.

You could still use a hasAndBelongsToMany association, and make use of the automagic "with" associations offered by CakePHP (1.2 only) to access the additional data in the HABTM table for relation type.

However, I'd be tempted to use:

• Person hasMany Relation
• Relation belongsTo Person, Relation, RelationType

In your app, is a persons relationship implied by someone else's relationship to them? I.e. if you find Person.id = 1 you might get all their relations, e.g. Person.id 2, 3 and 4. But if you find Person.id 2, you would not automatically get Person.id = 1, unless there was another record in the relations table that represented that relationship.

To achieve this, you can set up another assocation:

• Person hasMany Relation1 with foreignKey person_id
• Person hasMany Relation2 with foreignKey relation_id
• Relation belongsTo Person, Relation, RelationType

Note, you can achieve this dual relationship with HABTM too.