Scalability (Performance vs Maintainability) Repeater Matrix vs one big Table field vs Lots of table fields.

[alexm]

I'm in a bit of a fork in the road with a foot in both camps with a situation for something I'm working on.

I have an instance where I require perhaps one, or in some cases many different types of data to be store on a page which I would suit ProFields Table.

Now... I started by using tables as this keeps fields to a minimum and also is ideal for the purpose, however, there might be an instance where on one page there may be 20 different types of items which more or less follow the same data structure. So with this logic, I then have to create a table field for each type of item so I can split them out and keep records of them easily. Now, the various item types could be in the hundreds+. So then the unique field for each type route becomes bad for scalability and performance.

This is where I skipped the idea of using PageTable and having one ProFields Table field that handles all types, as I would still have to create a template for each item type if I want to clearly consolidate and store the different types separately on one page.

So then I settled logically on using RepeaterMatrix as I can create the various types and use the same Table field for the various type but have lovely separation on one page for the various types of items that should be grouped together. To the best of my knowledge, this would be the best way in terms of keeping things maintainable and also would scale better in general. Even though I have to descend another level to grab the data when I need it, there are means and ways to optimise this, such as joining the data I need in queries or even having fields on the parent page that are updated/recalculated (at intervals via cron or when one of the items table row's data changes). This would likely be much faster for calculations and saving lots of selectors??

Now.... Herein lies the question. Is there any impact (putting aside having a super long list of item types to manage and look at) performance wise to having LOADS of item types as ReaperMatrix item types so I maintain the separation?

This leads me to my last thought process, whereby I have one table field and add another column that is called category or type as the first column for instance and this stores the various item types. Then I end up with one field (though it won't be as some data structures are different enough that I think it wouldn't make sense to add loads of columns for those lesser common cases, as I'm just storing loads of empty columns then potentially) and potentially a couple of others for the edge cases where the data warrants a different Table field with the extra columns required.
The thing I don't like about this though is that it offers less separation for easily querying just what you want as you have to iterate over potentially a very long table and get a certain type every time you need to get that data from various pages based on type and also it will have loads of different types recorded within it.

Also it's worth mentioning that there may be thousands of child pages of a parent. The child pages being the page that will store the data for the various item types, which is why I want to try and find the happy medium between the most performant route vs the most structured, but with a strong inclination to lean towards it needing to be the best route for scalability performance wise.

Your thoughts are greatly appreciated!

Edit: I should add that I’ve read this thread: 

 

but although it appears to ask the same question, there is the intrinsic difference with the number of types of data being the key focus for me.

Cheerrrrrs all!

Edited August 4, 2023 by alexm