Proposal database structure custom tables

[remcotolsma]

Many WordPress plugins have already made the move to custom tables (WooCommerce, Easy Digital Downloads, Yoast SEO, etc.). We also have plans to switch to custom tables for the payments, subscriptions, etc. In an internal Basecamp to-do we made a first proposal.

Source: https://github.com/pronamic/wp-pronamic-pay/blob/develop/documentation/db.plantuml.

This design takes into account that in theory 1 payment can be linked to multiple subscriptions. Currently we still use $payment->subscription, $payment->get_subscription() and $payment->subscription_id. In other words, we assume that 1 payment is linked to 1 subscription.

[remcotolsma]

Subscriptions

DROP TABLE wp_pronamic_pay_subscriptions;

CREATE TABLE wp_pronamic_pay_subscriptions (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT(20) UNSIGNED NOT NULL UNIQUE,
    next_payment_date DATETIME DEFAULT NULL
) ENGINE=INNODB;

ALTER TABLE wp_pronamic_pay_subscriptions ADD FOREIGN KEY ( post_id ) REFERENCES wp_posts ( ID ) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT IGNORE INTO wp_pronamic_pay_subscriptions (
    post_id
)
SELECT
    post.ID AS post_id
FROM
    wp_posts AS post
WHERE
    post.post_type = 'pronamic_pay_subscr'
;

SELECT
    *
FROM
    wp_pronamic_pay_subscriptions AS pronamic_subscription
        INNER JOIN
    wp_postmeta AS postmeta
            ON pronamic_subscription.post_id = postmeta.post_id
WHERE
    postmeta.meta_key = '_pronamic_subscription_next_payment'
LIMIT
    0, 100
;

UPDATE
    wp_pronamic_pay_subscriptions AS pronamic_subscription
        INNER JOIN
    wp_postmeta AS postmeta
            ON pronamic_subscription.post_id = postmeta.post_id
SET
    pronamic_subscription.next_payment_date = postmeta.meta_value
WHERE
    postmeta.meta_key = '_pronamic_subscription_next_payment'
;

Payments

CREATE TABLE wp_pronamic_pay_payments (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT(20) UNSIGNED NOT NULL UNIQUE 
) ENGINE=INNODB;

ALTER TABLE wp_pronamic_pay_payments ADD FOREIGN KEY ( post_id ) REFERENCES wp_posts ( ID ) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT IGNORE INTO wp_pronamic_pay_payments (
    post_id
)
SELECT
    post.ID AS post_id
FROM
    wp_posts AS post
WHERE
    post.post_type = 'pronamic_payment'
;

Both

DROP TABLE wp_pronamic_pay_subscription_payments;

CREATE TABLE wp_pronamic_pay_subscription_payments (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_id BIGINT(20) UNSIGNED NOT NULL,
    payment_id BIGINT(20) UNSIGNED NOT NULL,
    CONSTRAINT subscription_payment UNIQUE( subscription_id, payment_id )
) ENGINE=INNODB;

ALTER TABLE wp_pronamic_pay_subscription_payments ADD FOREIGN KEY ( subscription_id ) REFERENCES wp_pronamic_pay_subscriptions ( ID ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE wp_pronamic_pay_subscription_payments ADD FOREIGN KEY ( payment_id ) REFERENCES wp_pronamic_pay_payments ( ID ) ON DELETE CASCADE ON UPDATE CASCADE;

SELECT
    *
FROM
    wp_pronamic_pay_payments AS pronamic_payment
        INNER JOIN
    (
        SELECT
            postmeta.post_id AS payment_post_id,
            postmeta.meta_value AS subscription_post_id
        FROM
            wp_postmeta AS postmeta
        WHERE
            postmeta.meta_key = '_pronamic_payment_subscription_id' 
    ) AS map
            ON pronamic_payment.post_id = map.payment_post_id
        INNER JOIN
    wp_pronamic_pay_subscriptions AS pronamic_subscription
            ON pronamic_subscription.post_id = map.subscription_post_id
LIMIT
    10
;

INSERT IGNORE INTO wp_pronamic_pay_subscription_payments (
    subscription_id,
    payment_id
)
SELECT
    pronamic_subscription.id AS subscription_id,
    pronamic_payment.id AS payment_id
FROM
    wp_pronamic_pay_payments AS pronamic_payment
        INNER JOIN
    (
        SELECT
            postmeta.post_id AS payment_post_id,
            postmeta.meta_value AS subscription_post_id
        FROM
            wp_postmeta AS postmeta
        WHERE
            postmeta.meta_key = '_pronamic_payment_subscription_id' 
    ) AS map
            ON pronamic_payment.post_id = map.payment_post_id
        INNER JOIN
    wp_pronamic_pay_subscriptions AS pronamic_subscription
            ON pronamic_subscription.post_id = map.subscription_post_id
;

SELECT
    *
FROM
    wp_pronamic_pay_subscription_payments AS subscription_payment
        INNER JOIN
    wp_pronamic_pay_payments AS payment
            ON subscription_payment.payment_id = payment.id
        INNER JOIN
    (
        SELECT
            postmeta.post_id,
            postmeta.meta_value AS start_date
        FROM
            wp_postmeta AS postmeta
        WHERE
            postmeta.meta_key = '_pronamic_payment_start_date'
    ) AS payment_meta
            ON payment_meta.post_id = payment.post_id
WHERE
    subscription_payment.start_date IS NULL
LIMIT
    0, 100
;

UPDATE
    wp_pronamic_pay_subscription_payments AS subscription_payment
        INNER JOIN
    wp_pronamic_pay_payments AS payment
            ON subscription_payment.payment_id = payment.id
        INNER JOIN
    (
        SELECT
            postmeta.post_id,
            postmeta.meta_value AS start_date
        FROM
            wp_postmeta AS postmeta
        WHERE
            postmeta.meta_key = '_pronamic_payment_start_date'
    ) AS payment_meta
            ON payment_meta.post_id = payment.post_id
SET
    subscription_payment.start_date = payment_meta.start_date
WHERE
    subscription_payment.start_date IS NULL
;

UPDATE
    wp_pronamic_pay_subscription_payments AS subscription_payment
        INNER JOIN
    wp_pronamic_pay_payments AS payment
            ON subscription_payment.payment_id = payment.id
        INNER JOIN
    (
        SELECT
            postmeta.post_id,
            postmeta.meta_value AS end_date
        FROM
            wp_postmeta AS postmeta
        WHERE
            postmeta.meta_key = '_pronamic_payment_end_date'
    ) AS payment_meta
            ON payment_meta.post_id = payment.post_id
SET
    subscription_payment.end_date = payment_meta.end_date
WHERE
    subscription_payment.end_date IS NULL
;

SELECT
    subscription.id,
    subscription.post_id,
    MAX( subscription_payment.end_date ) AS next_payment_date
FROM
    wp_pronamic_pay_subscriptions AS subscription
        INNER JOIN
    wp_posts AS post
            ON subscription.post_id = post.ID
        LEFT JOIN
    wp_pronamic_pay_subscription_payments AS subscription_payment
            ON subscription_payment.subscription_id = subscription.id
WHERE
    post.post_status != 'subscr_on_hold'
GROUP BY
    subscription.id
ORDER BY
    next_payment_date DESC
LIMIT
    0, 100
;