SQL table relations impossible!!

[GoogleCodeExporter]

It is impossible to create a table relation between the content_nodes 
table and the pages table without doing it row by row. For example, the 
following would be impossible:

Select all pages with content containing some value.

This is impossible because the content_nodes table cannot be directly 
related to the pages table by a FK / PK relation. This is because the FK 
in content_nodes is prefixed with the parent type.

This is especially frustrating as i would like to be able to use the 
existing table structure for a number of my modules but without being able 
to do this, searches are extremely inefficient.

What i would propose doing is seperate the content type and parent_id into 
two different columns.

content_nodes
- id PK
- parent_id (int) FK
- parent_type (VARCHAR) FK
- node
- ....

The FK becomes a compound between the two columns. 

I realize changing the table structure at this point is daunting. 
However,  since the framework is well designed, i think that very few 
changes would need to be made. An update script could update the 
content_nodes structure and values automatically. A few changes to the 
models and the system is ready to go. I would be more than willing to 
write this update procedure.

Original issue reported on code.google.com by barbedwi...@gmail.com on 19 May 2009 at 9:16

[GoogleCodeExporter]

it is not impossible to create this join; you can use CONCAT. with that said, 
the
structure that you mentioned is in line with the way that i often do this.

if you are interested in taking this on we would need the following:

* an updated page and content_node model
* a command (Digitalus_Command) so a user could upgrade an existing database 
* an updated installer 

This whole area will be refactored with version 2.0, using the full capacity of
Zend_Db_Table_Relationship as well as custom content item classes which provide
object oriented access to these data objects.

Original comment by forrestlyman@gmail.com on 20 May 2009 at 3:20

[GoogleCodeExporter]

My main concern is to be able to pull off the join. If CONCAT will work; fine. 
I 
would consider aiding in the refactoring if i was made part of the development 
team. 
What is the timeline for release on v2? If its scheduled for the somewhat near 
future, than perhaps i can just help with that development instead.

Original comment by barbedwi...@gmail.com on 20 May 2009 at 3:25

[GoogleCodeExporter]

this refactoring will hold off for 20

Original comment by forrestlyman@gmail.com on 1 Jun 2009 at 4:25

• Changed state: WontFix