First, the relevant bits of the table structure:

contact -contactID -email data -value -contactID -definitionID definition -definitionID -name

Each record in the contact table represents a single contact along with their e-mail address.

Each record in the definition table represents the definition of a custom data field. For example, there might be five records in definition - Organization, Zip Code, Comments, Address, Phone. In addition to the names of the fields, it also defines relevant meta-data about the fields.

Each record in the data table contains the value of the custom data field as it relates to the contact. Its definition is taken from the definition table.

To further clarify, if I wanted to generate a table of contacts along with their custom field data, it might look like this:

| E-Mail | Organization | Zip Code | Comments | Address | Phone | ------------------------------------------------------------------------------------------------------------- | sean@example.com | ACME | 12345 | Cool guy! | 123 Test St | 555-5555 | | sean2@example.com | SomeCo. | 54321 | Doesn't know anything about ORM! | 321 Test Blvd | 444-4444 |

The benefit of this system is that it can scale out as much as I need it to and is easily customizable. The detriment is that I have no idea how to define the relationships :)

I've attempted defining contact:data as 1:M and definition:data as 1:M , but the results seem a little odd: With two contacts and a single definition, adding a row to data for each contact and then calling entityLoad( 'Contact' ) results in an interesting relationship. It appears like this(just going to use some pseudo-struct notation because it's easy enough to type and, I hope, read):

{ contact: { email: 'sean@example.com', data: { value: 'ACME', definition: { name: 'Organization', data: { value: 'SomeCo.', contact: { email: 'sean2@example.com' } } } } }

It looks like it is creating an indirect relationship between contact and definition based on the data table's relation to both tables. As you can imagine, upping the amount of contacts and custom fields just makes the problem exponentially worse.

Is this type of relationship possible using CF9's ORM? How can I accomplish it?

Thanks in advance!

EDIT: Forgot to specify - I am using MySQL, if it matters.

EDIT 2: CFC definitions follow:

Contact.cfc

/** * @persistent true */ component name='Contact' { /** * @type numeric * @sqltype int(11) * @generator increment * @fieldtype id */ property contactID; /** * @type string * @sqltype varchar(50) */ property email; /** * @type array * @fieldtype one-to-many * @cfc Data * @fkcolumn dataID */ property data; }

Definition.cfc

/** * @persistent true */ component name='Definition' { /** * @type numeric * @sqltype int(11) * @generator increment * @fieldtype id */ property definitionID; /** * @type string * @sqltype varchar(50) */ property name; /** * @type array * @fieldtype one-to-many * @cfc Data * @fkcolumn dataID */ property data; }

Data.cfc