Performance considerations - postmeta table versus new table for custom posts with foreign keys?

Question

I am modifying a plugin that uses various custom posts types. The types are

courses
   lessons
      topics

At course, lesson, or topic level there can also be quizzes and assignments.

ALso the plugin integrates with bbpress so that there can be forums and forum topics associated with a course. But only at course level. FOr that reason I added relationship field via the cool pods plugin so that when I add a forum topic I can tie it to lessons and topics. But there is still the retrieval question...

I often need to do actions such as

1. Retrieve all quizzes for course (meaning regardless if quiz was attached to lesson or topic
2. Retrieve all quizzes for lesson (meaning both from lesson and associated topics)
3. Retrieve all forum topics for lesson (meaning forum topics that are associated with a lesson topic.
4. Retrieve all forum topics for course (meaning forum topics across the whole course, which means those tied to lessons and topics of the course.

It gets really tiring having to always nest for loops just to extract everything I need.

Moreover, the way wordpress plugins uses the wp_postmeta table is a bit alarming. I don't the see the point to a given set of data having to be bundled in with every other plugin's data.

This has me thinking I should do away with all the plugin data that is stored in wp_postmeta and instead create separate tables for course, lesson, topics, quizzes, assignments. That way for a quiz, I can set course_id, lesson_id, topic_id so that I can readily retrieve quizzes tied to course, lesson, or topic. Likewise for forum topics. That means there will be duplicate entries in quiz table. Like

id  quiz_id  post_id    id_type
1      2       5        course
2      2       12       topic
3      2       21       lesson
etc

But I won't have thousands of quizzes! I can't think of a cleaner way to extract data I need at the level I need. I really am getting tired of get_posts with args that don't allow me to get what I need in single query.

Curious how others deal with this. I am coming from laravel dev where I loved the fact how for a model I could define onetomany and manytoone entities. That way I could do

$courses->topics
$courses->quizzes
$courses->forums
$courses->lessons->forums
$courses->topics->quizzes

It is fantastic working that way.

Lately as I work through wordpress I get the impression that this approach has so many roadblocks due to the nature of the wp_postmeta table.

But maybe I am missing something. Maybe wp_postmeta is some beautiful thing and I am not seeing the beauty.

So - can someone advise a good way to retrieve data in the way I am seeking?

Thanks,

Brian

Answer 1

I can`t say this is the best solution but I know you can't go wrong with a structure like wp_postmeta.

I've done some custom work with GravityForms and they now use a similar structure. Instead of using postmeta there is an "entries" table. So with each form submission, there is an entry_id and multiple different entries differentiated by the meta_key. Knowing they switched to a structure like this probably indicates it's the best solution for queries.