data structure for n:n relation

[herr rilke]

Hello all,

I am thinking about the following requirement:

there are documents (pages) and users.

the user should now be able to mark a document as "read" by pressing a button.
when he then calls up the document again, he finds a "read" marking - so he knows that he has done everything here.

do i now create a new subpage via API, which only has the fields "pageID" and "userID" for that?

and would that be an extra query on each page load to see if the combination of userID and pageID already exists to display a marker?

 

[bernhard]

1 hour ago, herr rilke said:

there are documents (pages) and users.

How many documents? How many users?

If you only have a limited number then a simple page reference field would be all you need. Just add a field "readby" to your documents page and add users to that field via API.

You just need to make sure that you don't have too many pages stored in the page reference field. So if you have many documents but just a few users add the page field to documents. If you have many users but just a few documents add the page field to the user template and add read documents to the field.

If you have many documents and many users go with the custom table solution that I showed here: 

 

[herr rilke]

thank you, bernhard, that was very enlightening!
I think the third approach (with an own table) is the most suitable for me.
i also understood better how i can implement such an approach.

[MarkE]

On 9/9/2023 at 9:31 AM, bernhard said:

So if you have many documents but just a few users add the page field to documents. If you have many users but just a few documents add the page field to the user template and add read documents to the field.

Alternatively do both and use @Robin S’s ConnectPageFields module. Then you can access the link in either direction without a foreach loop. 

[herr rilke]

wunderful!
thanks for the hint!

that does the job exactly as otherwise done with SQL

[bernhard]

Note that this does NOT help with scalability issues. Quite the contrary. It will keep two page fields in sync but it will store the data twice. So if you have lots of pages, you'll make your issues even worse.

[herr rilke]

ok, good to know!
thanks for sharing!

there are approximatley 100 documents and > 200 users.

so i think i'm heading to the SQL solution.

[bernhard]

If you compare both approaches it would be interesting if you share your findings. https://processwire.com/blog/posts/debugging-tools-built-in/ (Debug::timer) might be helpful and memory usage could also be interesting.