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.