You should be skeptical of anyone who says that there is a single “right” way. The right way depends on the situation. Using the CPT infrastructure has a number of notable benefits:
- You get the Dashboard UI for free
- You automatically take advantage of WP’s caching, including any persistent cache plugins that the installation may be using
- You automatically get goodies like post revisions
- You get access to the
WP_Queryclass, which means that, in theory, you don’t have to write any (or at least not much) likely-to-be-buggy-and-vulnerable-and-inefficient SQL - If you’re planning on distributing the plugin or opening it up for open-source development, you may find that developers are more comfortable using custom post types and the associated API functions than your own custom stuff
The problems with the CPT API mostly stem from the fact that it is highly married to the metaphor of ‘posts’, and all the aspects of that data type that come along with the metaphor. From the MySQL command line, run DESCRIBE wp_posts. WP assumes that your content has a title, that it has a (single) author, that you only need to keep track of the created date and the last-edited date, that you’ll need space for an unindexed post_content, etc. This works well for some kinds of content, but not necessarily for others. You’ve already gestured in the direction of some potential problems:
the number of post metafields I’ll need for my extra fields per cpt if I go that route, and if that will make things “tricky”
There are two ways to augment the wp_posts schema through the CPT API: postmeta and taxonomies. Postmeta is unindexed key-value pairs, which is great for storing a bunch of miscellaneous data, but not at all optimized for doing complex lookups. Taxonomies are somewhat more flexible in this regard, but you’ll still face lots of potentially costly subqueries if you have very complex lookups. (The meta_query and tax_query arguments and their query constructor classes are very nice and handy, though.)
If, as you suggest, you only need to do these kinds of “semi complex relational filters” in the case of occasional reports, then this architecture is probably OK for you. It’s when you start exposing the filters to users, so that you have to run many complex JOINs and subqueries all the time, that things get out of hand quickly.
how to best manage relationships, especially if I have many to many relationships
Many-to-many relationships are a longtime sticking point in the WP dev community (see https://core.trac.wordpress.org/ticket/14513). You can fake it without using custom tables by mapping taxonomy items onto post_ids (so that, eg, you can say that ‘P3 has the relationship Y to P5’ by saying that P3 has the tag ‘Y-P3’) but this gets confusing (and inefficient) very quickly. You also may consider creating your own relationships table that links together CPTs – you’d still get the benefit of CPTs, and only be creating a single DB table. For a nicely-executed version of this method, see the Posts 2 Posts plugin: https://wordpress.org/extend/plugins/posts-to-posts/
So, in the end, you should decide based on:
- The kind(s) of data you’ll be storing – how “post”y are they
- The kinds of queries that will be required – how complex will they be
- Scale – how complex is your desired schema, how many total objects will you have, and how many users do you anticipate
If the answers are: Very posty, not too complex, and don’t have to scale super-huge, go with CPTs. Otherwise consider your own tables.