Page Structuring Approach Considerations (ecommerce order example)

[Jonathan Lahijani]

I'm developing a very advanced ecommerce site with ProcessWire and an order needs to have various related pieces of data.  To keep it simple for this example, an order should log payment attempts ('payment' template) and the items that were fulfilled ('fulfillment' template).

Let's assume our order is here in the page tree, where 123 represents a page using the 'order' template (which contains the billing address and things like that): /orders/123/

One scenario that I often come across when developing ProcessWire sites with strong data relationships is where to put related pieces of data.  In the situation I described, I could do it in one of three ways:

Approach 1: as child pages of 'order' template
I could make payments and fulfillments as children of the 'order' template.  This is a ProcessWire-y way to do it.

Approach 2: under one separate master page
I could make payments and fulfillments as children of /order-updates/ and establish the relationship using an 'order' page reference field on the 'payment' and 'fulfillment' templates.  This is a blend between traditional database design and a ProcessWire-y way to do it.

Approach 3: under multiple master pages
I could make payments as children of /payments/ and fulfillments as children of /fulfillments/ and establish the relationship using an 'order' page reference field on the 'payment' and 'fulfillment' templates.  This is a traditional database design way to do it.

What are your thoughts in regards to this specific example?  Do any of them have pitfalls that I don't foresee?  If I had to switch from one technique to another, that would be easy to do with a simple migration script.

[MarkE]

I would think it all depends on the cardinality of the relationships. If one-many then children are fine. Otherwise group separately and probably use ConnectFields module. 

[Ivan Gretsky]

We need to think about pros and cons when choosing. The pro arguments for 1 is that you have all data in a page tree visually related and that you can easily delete all related data deleting the corresponding order branch.

As always, these pros are also the cons for the the opposite solution (3). Choosing 3 you can more easily see the lists of payments and fulfillments records. You can delete orders without deleting connected payments and fulfillments records if you need, but you also have to deal with the housekeeping when deleting by yourself.

If you build the dedicated UI for all that in admin it might not even matter.

As for me, feel inclined to go the 3rd root and do not see any arguments going the 2nd.

[MarkE]

10 hours ago, Ivan Gretsky said:

You can delete orders without deleting connected payments and fulfillments records if you need,

Another reason for doing some deep thinking about the entity model before choosing. If payments can only ever belong to an order (and only one order) then they should be children of the order otherwise they risk being orphans.

If payments can cover more than one order, then they should stand alone, but you need to do serious thinking about how they are allocated to each order if the amounts don’t match exactly. This might be achieved via an “allocation” field (float or decimal) in the order and the payment. And/or you might need to split orders down into order lines. 
An accurate understanding of the business model is required. Plus how it might evolve. Then do the entity modelling. Lastly decide how to implement in PW. And don’t forget that @Robin S‘s excellent ConnectFields module is just the biz for maintaining many-many relationships (e.g. between the two allocation fields referred to above). 

[teppo]

12 hours ago, MarkE said:

This might be achieved via an “allocation” field (float or decimal) in the order and the payment.

Perhaps obvious, but do not use float for money. It will result in rounding errors.

Also, regarding allocations (if you go this way), consider early if you need more than one per payment. E.g. if you need to allocate payment per invoice/order row, or allocate overpayment separately, etc. And also give some thought to how you'll handle returns/rebates etc.

(In my experience two-way sync is rarely necessary, since PW makes querying data easy anyway. But you will likely need some hooks to keep things like order payment status in line with payments.)

I recently built a system that handles event registrations and product sales, and my approach was basically "approach 3".

One question is if you need to also handle accounting reports, and at what level. Honestly this is all relatively simple until debit/credit entries, cost centres, accounts, etc. get into the picture. In my case I do need to handle that, so each order/allocation logs a separate accounting row, which I later use to produce a sensible report. Including actual time of each event, which is super important here.

[matjazp]

2 hours ago, teppo said:

Perhaps obvious, but do not use float for money. It will result in rounding errors.

What is your advice? I've read an article long time ago but forget what conclusions were made...

[szabesz]

1 hour ago, matjazp said:

What is your advice? I've read an article long time ago but forget what conclusions were made...

Use decimal, already in the core, see (FieldtypeDecimal uses InputfieldFloat):

https://github.com/processwire/processwire-requests/issues/126

https://weekly.pw/issue/367/ "Float and integer fields are now considered compatible with the newly added decimal field, making it possible to convert these from one type to another."

If you feel like applying custom formatting in the admin, see :

 

[szabesz]

1 hour ago, szabesz said:

If you feel like applying custom formatting in the admin, see :

BTW, on the frontend I use this https://github.com/brick/money which works well. It is a bit verbose to do math like that, but worth the hassle.

Edited March 22, 2023 by szabesz
EDIT: not just on the frontend but for any money related math

[MarkE]

Another thought, you may want to record invoices separately from orders, to deal with unsatisfied or partially satisfied orders. Alternatively this might be handled by status fields on order lines. At the risk of repeating myself, getting the business specification right is paramount. 
PS I absolutely agree with @teppo regarding decimal fields. A slip on my part.