Ideas on how to create data relationships (one-to-one,one-to-many, many-to-many)

Despite all of the many strengths of MODx, one thing it lacks is a native way of creating data relationships, so I’m going to propose a way to do this. [The topic of data relationships between documents/objects/resources, etc. in MODx was mentioned in a previous thread (http://modxcms.com/forums/index.php/topic,28190.0.html) but I think it deserves its own thread, so I’ve begun one here.]


STORING RELATIONSHIPS

First of all, we need a table that can accommodate the relationships. I’ll call it modx_site_relationship_data. I would create the following fields:

* id
* id2
* rank

where id and id2 contain the MODx IDs of documents. This structure can accommodate one-to-one, one-to-many, and many-to-many relationships (e.g. there will be an entry in the database for each relationship, possibly resulting in several entries with the same value for "id", with a theoretically infinite number of values for id2). That’s a starting point.

The "rank" field adds the ability to rank order the relationships, to force them to appear in a custom order.

If we want to extend the possibilities, we could either create extra fields in the modx_site_relationships_data table or create extra table(s) to allow for relationships between objects that don’t have MODx ids (files, urls, etc.). This is especially relevant in the next generation of MODx where files are visible in the tree structure.


DEFINING RELATIONSHIPS

We’ll need a table to define the type (one-to-one, one-to-many, or many-to-many) and parameters of the relationships. I’ll call this table modx_site_relationships. Parameters can include some ready-made options, but also allow for custom options. Examples of some useful ready-made options are:

* all documents with a certain template (or templates)
* all documents with a certain parent (or parents)

It would also be nice to have a built-in "where" option so that users could type in things like "where id < 200" or "where pagetitle like ’%2008%’, etc.
For non-standard situations, users would also be able to write custom PHP snippets to specify custom relationship parameters.

There would be a few options for displaying the relationship options when editing the page. Some possibilities include:

* as checkboxes
* as radio buttons
* as a single-select drop down list
* as a multi-select drop down list
* as a text input, for typing in the ids of the documents as a comma separated list

So the fields for this table would be something like this:

* name (the name of the relationship)
* type (one-to-one || one-to-many || many-to-many)
* parameters (from a list of ready-made parameters)
* where (simple filter for the ready-made parameters)
* custom_parameters (referring to a custom snippet)
* control_type (checkboxes || radio buttons || single-select drop down... etc.)


ASSOCIATING RELATIONSHIPS WITH TEMPLATES

It would probably also be wise to have specify relationships at the template level. This would require another table (e.g. modx_site_relationships_templates) that associates the relationship with templates. It could include the following fields:

* template_id
* relationship_id


CREATING RELATIONSHIPS

Users would create relationships when editing or creating documents in MODx. They would see the control type specified in the modx_site_relationships table, and potentially would see a list of documents to choose from.

This would be the easy part! And this is where the benefits really begin to show.

(Note: When chosing from multi-select lists, one thing that I think will be very important is to have a JavaScript or AJAX control that writes to the page as soon as items are selected, by displaying a new line of text for each relationship as soon as it is formed either above or below the list, with an "X" by them for easy deletion. Othewise, users have to remember to hold down the CTRL/option key while selecting items, and may make a mistake and accidentally de-select them all.)


RETRIEVING AND DISPLAYING RELATIONSHIPS

We’d have to come up with a new way of retrieving and displaying relationships, because until now, all of the MODx tags (e.g. for TVs, links, system settings, etc.) refer to a single value, not to a list/array of values. We would need to create a new kind of tag that specifies a relationship and allows for parameters. Here’s an example of displaying a list of instructors for a course (using syntax more like the next generation of MODx):

Explanation:
* The "[[@" is a tag I made up to specify an array or list (any syntax could be used; it doesn’t have to be "@")
* "course_instructors" is the name of the entry in the "modx_site_relationships" table
* Notice that the "<ul>" and "</ul>" tags are outside of the reference to the relationship
* Notice also that there is only one list item within the relationship tag, because this information will be looped.
* The name of the table (or I guess it would be the name of the template) is specified, to ensure we get the data from the right place. We could place document data from either side of the relationship within the call to the relationship (e.g. [[*instructors.email]] and [[*course.title]])

It should also be possible to put snippets, PHx syntax, or other types of information within the array object. Here’s a quick example:


Final Comments
The exact details of this proposed solution could be worked out and modified, but I believe this is a solid start to solving an important MODx weakness.

It occurs to me that when retrieving and displaying relationships, we’d want to have the flexibility to limit how much is retrieved and displayed, and maybe add other parameters. The default would be to retrieve all of the information (id, pagetitle, longtitle, content, etc. plus all TV values), but for performance optimization, we could limit it, doing something perhaps along these lines:

This makes the syntax look and act more like a snippet, but it would be a native snippet in the MODx core explicitly for working with arrays and lists.

paulb, this sounds like a great idea for an add-on for MODx, but this will not be a part of the core. And there is no reason for it to be, as this can all be accomplished in the current system using user-defined snippets, plugins and custom tables, without adding any overhead to the well optimized CMS engine that does what it was designed to do rather well. I still think you are trying to use certain aspects of the product for purposes other than they were intended to serve, rather than exposing an "important weakness" in the core of MODx.

As I’ve mentioned several times, xPDO is a tool for representing robust domain models with rich relationships that you can define and then easily integrate where it makes sense in any presentation layer, like MODx or Smarty. Not all data belongs in the presentation layer, and custom requirements like you are describing can be accomplished in any number of ways without touching the MODx core. We have a lot of users who have found their own way to work with the freedom that MODx provides, and we have a big job balancing the evolution of the product with the compatibility issues that will arise as we change core data structures to support an extensive list of improvements and new features. This is something that xPDO will also simplify moving forward by providing interaction with MODx domain objects separate from the relational model representing the actual database tables (i.e. code can and will be independent of specific database column or table references), and another of many reasons why I use it exclusively when building all kinds of custom web applications in any version of MODx past or present.

We do envision an expanding toolbox of extensions with ways to manage entire custom data models and facilities for extending the MODx core data model itself in certain places (e.g. you’ll be able to provide core extensions which provide a custom class representing a document). I’m in no way trying to discourage your ideas for solving these problems; again, I think you can resolve this without ever touching the core, following the same basic logic. I just want to clarify how the challenges you are describing are very much being addressed in the long-term plan for MODx Revolution, based on xPDO, and that the approach we have chosen IMHO makes the most sense in consideration of many other new features in the works, and for the MODx Team’s limited resources to focus on.

I may be using MODx for something that was not originally intended, but it’s not a very big stretch to make it do these other things.

I see data relationships as a core issue rather than a peripheral one to be addressed with optional add-ons... especially when it comes to the input controllers. I would really like to be able to specify relationships when creating templates, and choose input controllers to handle those relationships from within the page editing/creation interfaces. If these input controllers are not available as part of the core, I have to come up with some back door approach, like modules or intranets or editing the database itself. I’d rather be able to edit the data in the context of the web page interface.

In terms of separating presentation from data, in my experience, most major pieces of data have corresponding web pages. Courses have pages. People have pages. Events have pages. News items have pages. There are a few pieces of data that don’t need pages. I guess I don’t have a page for every room in every building (though I suppose I could), but most web-based data is connected to a page. That’s why it makes sense to me to at least allow for a more unified data/page system, even if it doesn’t fit every circumstance. Why make a call to a separate database that has exactly the same number of items as there are MODx pages with the corresponding template? Whenever I create a new entry in the external database, I have to also create a new page in MODx. Why not use just one data repository, rather than two? This allows me to create both the page and the data at the same time, seamlessly. It also allows for more tightly-integrated data retrieval, whether for displaying the content on the web or just making printable reports of the data.

I’m not saying that I want to force the all-in-one approach on MODx for all circumstances -- that would go against the open box idea that you’re trying to promote, and which I like -- but I do want MODx to be able to handle it natively and more elegantly than it does now.

Nothing I say here is meant to detract from what has been accomplished with MODx, which is a lot, and what is coming in future releases, which I’m excited to use. I’m sure that xPDO and the other developments that are coming will be powerful and important, but in terms of making relationship data easily retrievable, I still believe we need to add those extra database tables, or something similar to them. Data relationships between objects and data types -- which have corresponding web pages -- are so very common. We already have the native parent-child relationship, which is natural and one of MODx’s great strengths (though I’d still like inheritable properties to be cumulative). Relationships across data types are just as natural and common -- and just as connected to their corresponding web pages -- but I have to find workarounds in MODx if I want to integrate the "pure data" (as if there is such a thing) and the "web view" of that data.

I’ll eventually make my plugin public (it’s still too idiosyncratic right now), but it will have the significant limitation of not having native input controllers to work with in the page editing interface. It’s more of a bolted-on product than an elegantly-integrated product. That’s the part that I want to change.

Hi paulb,

I’d really recommend starting to tinker with the Revolution code base which is built on top of xPDO. I think you’ll find you can have your cake and eat it too there. Custom manager pages for your specific data handling requirements and bespoke table structures too. While really interesting and tempting for the Evolution branch, the honest truth is that there’s just not enough bandwidth to consider how all of this would impact all of MODx, as it’s been pretty thoroughly poked, prodded and developed for about 4 years now. Can you imagine how the reception would be if it created problems that we didn’t catch before a release?

That said, I can’t wait to see your plugin!

Quote from: paulb at Aug 22, 2008, 11:02 PM

Why make a call to a separate database that has exactly the same number of items as there are MODx pages with the corresponding template? Whenever I create a new entry in the external database, I have to also create a new page in MODx.

This is the key point where we differ paulb; I’m suggesting not creating a document for each external table record, but instead, one single dynamic view document that presents the data from the external record rows in a single visual template.  All it takes is one document and a script to dynamically get the external data to put into placeholders and bam, you are done.  Nice and simple, efficient, and all of this complex relationship stuff is delegated to the external data structure.  This is the heart of what I am talking about when I say separating presentation from data and business logic.

Ultimately (and sorry to beat this into the ground, but I’m passionate about this stuff  ) my point is, the less unnecessary data and logic you burden the content management engine with when it is working to present your views, the better chance you have of building a dynamic web application that can scale and perform optimally. Likewise, for your data to be most useful, why burden it with all the content management overhead; your data will be more reusable on it’s own rather than modeled in a visual presentation system.

I’m suggesting not creating a document for each external table record, but instead, one single dynamic view document that presents the data from the external record rows in a single visual template.

Yeah, there is that way of doing it, but then if I want friendly URLs for all of the pages, I’ve got to create my own custom URL rewrite scripts for each situation, and make sure they don’t conflict with the MODx URL rewrites. Maybe it wouldn’t be that big of a deal to do, but with a system already in place in MODx, why not take advantage of it?

Also, I still believe that it can be appropriate to create relationships between web pages that may or may not be a part of external data structures. An obvious example is a "see also" or "related web pages" feature. On the simplest level, this would be a one-to-many relationship where the page you’re on is related to, say, 5 other pages. Cross-referencing between pages in multiple directions, with "see page x instead", categories, and perhaps keywords -- things like that -- could create a rather complex set of relationships quite quickly. It may not be the most common of scenarios, but pages do have relationships to other pages.

And if your response is that it’s really the data on the page, not the page itself that is related to other content, that may be true in many cases, but not all. Pages that aggregate content from multiple data sources can be related -- as a whole -- to other pages in a way that the parts and pieces may or may not be.

To back up a bit, my first time creating a large complex web site using MODx, I used custom tables to accomplish my goals. That was my instinct, and it worked well enough, but I gradually became more and more concerned about the sustainability of all of my custom data input pages. I no longer work at the same place, and the person in charge there now doesn’t have the programming skills to maintain the interface. Plus, custom interfaces -- no matter who develops them -- are notoriously idiosyncratic and difficult to upgrade or port over to other situations. They fit the initial situation very well... until the situation changes slightly. Then it’s a pain to go back and make all the changes to the structure that coulda’ shoulda’ been in place to begin with. But in the real world, there are time constraints and shortcuts that we all take.

So, after this experience, I thought I’d see if I could work within the MODx system to accomplish the same goal. I built an equally complex site using the same basic data structure, but using templates and template variables as my pseudo tables. Along the way, I had to create a plugin that creates derivative tables that mimic my previous approach for pages that call in a lot of related data, but for much of my content, all I need is the MODx structure.

I feel pretty good about what I’ve done, but I lament the fact that my plugin is the weak link in the project, because it depends on me. It’s a custom solution, with all of my quirks and shortcuts and shortsightedness, as pretty much all custom solutions are. There is much that can be done to improve it, especially if I am to make it a general purpose plugin that others can use. It will take a lot of reworking to get it to that point. So the sustainability of what I’ve done hinges on my ability to improve my custom solution.

If relational data abilities were built into MODx, I could rest more assured that the project would be much more sustainable. There will always be a need for custom interfaces for complex challenges, but all I want to do is relate pages to other pages. I’ll deal with the difficult custom challenges that only my project faces. But I know I’m not the only one who has ever wanted to relate one page to another.