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
;