How do you make a list and list item relation if custom post types?

Question

I am trying to develop a plugin to manage and display lists, similar to a to-do-list. Different users have access to different lists and items. I started by making a custom post type for the list items. And now I am working on a custom post type representing the lists.

I was thinking about doing something like described here, where I have a dropdown in the "item" CPT and you can select a list/lists for it. The items have a postmeta field with the id(s) of the list(s). My guess would be have a separate database table that deals with the relationship of the lists and items.

Here is the part where I struggle: A list has obviously multiple items, but any item can also be on multiple lists. How can I realize that I easily get all the items for a list and also all the lists for each item? With the method described above, it's easy to get all the lists for each item by just looking up the postmeta field with the lists for that item. But the other way around seems more complicated since the postmeta field contains a serialized array.

I have an idea how I would solve this problem with a "normal" relational database, but I cannot figure out what would be a good approach to do this with WordPress.
Is the approach with the two CPTs not suited to start with?
My guess would be to create a separate additional database table that handles the relationship between the lists and items.

Answer 1

Here are few ideas how to relate content together in WordPress. Without knowing (and understanding) all the details and requirements it's difficult to give a complete answer, but these hopefully help you explore different possible solutions.

1. Store relation data in CPT post meta. You can then use WP_Query with meta parameters to find matching posts
2. Manage relations with post_parent property of WP_Post (it has its own column in DB {prefix}_posts table). Nothing stops you from relating different posts types together like this, if you handle the data saving yourself with wp_update_post()
3. Create a custom taxonomy to serve as lists and use CPT as items
4. Use CPT as list and store list items as serialized array / json in the post's post_content property
5. Skip CPTs altogether, store everything in custom DB tables and manage data with $wpdb

P.s. please note that these kind of open questions usually don't have a single definite answer, but recieve many opinion-based answers each with many ifs, buts and maybes, and your question might get closed.