Page 1 of 1

need DB join table advice

Posted: Fri Feb 15, 2008 10:31 am
by georgeoc
Hi all,

I'm experimenting with Doctrine at the moment to get me on the road to ORM. I've posted my question on their mailing list, but as it's more a DB design issue than Doctrine-specific, I thought I'd post it here too while I wait for a response.

Scenario:
- a Chain has many Elements
- Elements have one Chain
- Elements are one of a number of types: POP3, SMTP, etc.
- Elements need a db table for each type as they have differing fields
- saving a Chain should save all its elements in the corresponding table - save POP3 elements in the POP3 table
- loading a Chain should load all linked Elements as correct object type - e.g. a POP3 element loads as a POP3 object, not an Element object

I have experimented with inheritance, but although in principle it seems like the best design, I don't think it's going to work for me. The reason is that my elements are all of a different object type, and have differing requirements for fields. So I'm happy to use a join table and apply some extra logic when loading and saving Chain objects. I'd like some help on where to put that logic.

Proposal:
- Chain (id) (hasMany Element)
- Element (id, chain_id, element_type, element_id) (hasOne Chain)
- POP3 (id, username)
- SMTP .... etc.
In the Element table, fields 'element_type' and 'element_id' correspond to the name of the specific element table (e.g. POP3) and the id key of a table row.

Requirements:
saving a chain:
- imagine the $chain->elements array has one POP3 object
- for each of the $elements array, create a record in the corresponding table - i.e. create record in POP3 table
- create record in Elements table to link POP3 row with Chain row

Code: Select all

$pop3 = new POP3;
$pop3->username = 'george';
$chain->elements[] = $pop3;
$chain->save();
loading a chain:
- load Chain object
- query Element table for all rows with matching chain_id
- foreach element, load correct element based on element_type and element_id (e.g. load POP3 object with key '1')

Code: Select all

$chain = $q->from('Chain')->where('id = ?')->fetchOne(array($chain->id));
assert($chain->elements[0] instanceOf POP3);
assert($chain->elements[0]->username === 'george');
issues:
- deleting a Chain must delete all related Element rows, and all related POP3 (etc.) rows.
- updating a Chain must update all related Element rows, and all related POP3 (etc.) rows, delete any orphaned POP3 or Element rows and add any new ones. I'd imagine the safest way to do this is to delete all existing element records first, then save all the new ones.
- I don't know how to link POP3 with Element. I can write POP3->hasOne('Element'), but don't want to write Element->hasOne('POP3') as there will be many types of element (POP3, SMTP, etc) to be added dynamically and at a later time.

I am a bit of a novice with DB design, as you can probably tell. I'd appreciate some advice on how best to do this - not in Doctrine terms but for the join table, if indeed I need one. Am I on the right lines already?

Thanks!

Re: need DB join table advice

Posted: Thu Feb 21, 2008 11:00 am
by yacahuma
If you are not going to search on the actual dataI , I would do everything on just one table and save the data as xml or csv

so my db design will be
id|parent id| object-type|data
--------------------------------------