Node to node relationships module

Hi Nedjo,

This is, in my opinion, the absolute most important feature that we could possibly add to Drupal at this point. I already use Drupal as a rapid-prototyping system for custom data models, but I always write custom modules to handle relationships. As you know, while easier than with some web frameworks, it is still a time consuming task.

Here are some thoughts that I've had about what this functionality might look like:

• The relations must be defined in metadata. It is not enough to be able to start at a node of your choice and 'link' it to one or more other nodes of your choice. Rather, one must be able to say in advance that Companies have Employees and that Employees have Addresses, for example. Then, Company nodes would automatically offer the ability to link to Employees.
• It must be possible to distinguish between 1.n, n.m and, ideally, inheritance relationships.
• The relationships should be bi-directional; a Company is aware of its Employees and an Employee knows for which Company s/he works.
• It should be possible to make changes to the data-model of a live system with minimal and foreseeable affects. For example, adding a data type PensionPlan and linking it to Employee should automatically add the possibility of choosing a pension plan to Employee screens (the default ones, at least). Deleting the PensionPlan data type should clean the tables relating to Employee of all PensionPlan data.

I feel that Flexinode is a great module, and that its interface for defining node types should be used for development in this direction. I do not feel, however, that the data should be stored in serialized form. I think, rather, that database tables should be automatically created and maintained. So, when I create types Company and Employee, these tables would be created in the db. We would have a table Types which would track the different object types (Type_id, name, [plural], inherits_from).

If I then said that a Company can have Employees, an entry in the Links table would be made; (link_id, type_id_1, tpye_id_2, single_or_multple) or (1, 'Company', 'Employee', SINGLE). A foreign key column would be added to the Employee table as well. The Links table is necessary for all sorts of things. For example, making sure that Employee screens offer the possibility to link to a Company. It is also necessary for the administration interface so it can show which relations have been created.

Then, if I change the relationship between Company and Employee to be n.m (as a person sometimes works for more than one company), we would update the Links table (1, Type_id_1, Type_id_2, MULTIPLE), create a Company_Employee table (Company_id, Employee_id), look in the Employee table for an existing foreign key, and finding it, move the links to the Link table. In other words, the system should be flexible enough to manipulate existing data intelligently.

There are other considerations as well. We will need a Fields table to track every individual Field in these automatically generated tables. Why? Because, depending on data type, we will want an intelligent default user interface for data entry. We will need to have a table like this; (Field_id, Type_id, Data_type, [options...]).

Taking the above mentioned approach would have many benefits. A large amount of SQL would be moved out of the insert, delete and update hooks of individual modules and pushed down to a lower level. It would be unnecessary to make contrib modules just to represent a data object. Making tabular representations of the data would be easier than with Flexinode where the data is serialized. Drupal would shine as a web application framework and we could throw away the term CMS.

I have used a framework that does exactly what I've described above and the potential is astonishing. An auto manufacturer was holding a beauty-contest to award a major contract for the development of a web application. The three companies in the room were IBM, T-Mobile (I think) and the company I was working for at the time (I wasn't there). During the day-long presentation from the auto manufacturer, my colleagues used the data modelling system (like described above) to translate what was being demonstrated into a data model. All three companies had a chance at the end of the day to make a short presentation about what they had to offer and what experience they had in the specific field. The first two contestants showed a lot of credentials and some very impressive PowerPoint slide shows, and my colleagues showed a functioning web application which covered 100% of the data collection needs and ca. 60% of the workflow - already implemented. The other two companies never submitted final proposals - the contract was awarded on the spot.

Anyway, I diverge. The point is, with Drupal, I'm already able to make applications in a week or two that others make with things like Struts in a month or two. The ability to manipulate a data model and show relationships without having to write the SQL every time would be a great boon.

- Robert Douglass

-----
visit me at www.robshouse.net

i often have this need as well. one approach worrth exploring is to create a new field type for flexinode (i.e. an include file) which shows a dropdown of options to the user. Instead of having the admin define each option though, we let the admin define a SQL query returns the option names and values. The admin should decide if one or more can be selected, and maybe some more bits.

Then flexinode will present the fied to the user and save the data. Sounds simple enough ...

In the past I worked with a product named 'Rochade' (http://www.rochade.com), that was a kind of network database.
It let you create relations between items and specify the versus of the relation, so it was possible to define a start and an end and traverse all the tree displaying all the affected items.

The syntax was proprietary, but, in a node you simply had to specify the number of the items connected, and the versus (with a simple > or <)

Adding such a feature to Drupal would be FANTASTIC !!
You could easily creates sites like Linkedin (www.linkedin.com)
where you can manage relations...

I can help with the development (if time permits)

Matteo

Thanks for the suggestion, but I tried clipper with 4.6 and had difficulties. Has it been updated or should it work with 4.6? I would be glad to try it again.

As for node relativity: is there really an issue with it under 4.6? It seems to be working! I've just installed it, deciding that I did not want to assume incompatibility based some rather ambiguous forum posts. I'm glad I've tried it, because I have zero problems with it so far.

I'm still wondering how I might get these relationships to display in the flexinode-table view (or similar view)? Any ideas?

Thanks again for your input.