New order - object relationship table for storing order associations to other objects

[ashleyfae]

Enhancement Request

Explain your enhancement (please be detailed)

There are three add-ons that all do the same thing, in the same way, and all have the same problem: scaling.

These plugins store a relationship between something in the plugin and orders. For example:

• Software Licensing: Has "licenses" and stores a relationship between an order and a license.
• Recurring: Has "subscriptions" and stores a relationship between an order and a subscription.
• All Access: Has "passes" and stores a relationship between an order and a pass.

Each of those "objects" (license / subscription / pass) can have one or more payments associated with it.

Right now they all store this association in meta tables somewhere (either postmeta with the payment -- soon to be order meta -- or licensemeta). However, they all have the same problem in that these queries are slow and do not scale well.

The idea started in Software Licensing -- we'll just create a new database table with a simple schema:

license_id payment_id (and payment_type was also proposed to designate if it was a new payment, renewal payment, or upgrade)

However, then I realized Recurring had the same problem, with the same possible solution:

subscription_id payment_id

And then I realized All Access also had the same problem, with the same possible solution:

pass_id payment_id

It seems silly to create three separate database tables to all solve the same problem, so I'm wondering if instead we should create a single table in EDD core. Quick schema idea:

order_id (working with EDD 3.0 lingo now) object_type (could be subscription, pass, license) object_id (would reference the subscription ID, pass ID, license ID)

Justification or use case

Improved performance in add-ons that need to store payment relational information, without using order meta, which doesn't have an index on the meta value.

Also, by putting this in EDD core, we avoid repeating ourselves by building the same database table in 3 different add-ons for ultimately the same purpose.

[cklosowski]

I rather like this idea. It turns it almost into a terms relationship style table.

[louisgan]

edd_get_payment_meta() and edd_update_payment_meta() are having slow performance.

edd_get_payment($payment_id) will be called in the above functions which then goes through the whole setup_payment() method.

would be good to allow a payment object to be passed in or a direct SQL query.