Store data in Processwire as pages, or in a separate database?

[Elko 2]

Hello group,

I would like some input on how to store the data for a project I'm currently working on. 

It is a Backoffice application for teams, where each day is divided into 4 sections, representing the eight working hours in a day as two-hour time slots.  The time slots will be filled with a letter and a color, indicating the task that the team member is appointed to at that time.  I have attached some screenshots so you can get a general idea of what it looks like (tried to make a GIF screencast with 'peek' but the file turns out too big every time to post here).

The attached files show the Backoffice application itself and how I organized it in the back end of Processwire.  Teams are pages, containing functions in the team.  The members of a team are implemented as an ASM select field on the functions pages, that point to the Admin->Access->Users pages.  Every user has access to their own profile page in the back end, but no other pages are allowed for them.

As for storing the time slot data.  I could create a separate database, or use Processwire to create trees of pages --under the Users pages-- with the date timestamp and task ID for that day, but I don't know yet how that would scale in Processwire when there is more-and-more page data in the Backoffice over time.  With many team members, that tree could grow quite large, because it has to also be kept there as an archive for making reports about the working time the teams and members spent on what tasks in the past.

So, I would appreciate some input from you on how you would store the time slot data in a case like this.

Kind regards, Elko

PS: I've been using Processwire since March this year and really like it so far, so thank you for making a great product like this.
 

[Robin S 7,076]

Welcome @Elko!

From comments that I've seen here in the forums I know that there are people using PW successfully with millions of pages. So that might mean that there is nothing to worry about if you decide to use a page for every time slot for every user.

But personally speaking, when I've had situations like this it has felt a bit wrong to use large numbers of pages to store such simple data and I have used Profields Table for this sort of thing instead. This isn't based on any tests I've done - just on gut instinct. I have a feeling that if you need to regularly and quickly load and process large numbers of records then pages can be a bit less than optimal. @bernhard's RockFinder module might be useful to avoid loading full Page objects:

Or if you're comfortable working with databases then there's no reason why you couldn't use an external database (or just an extra table in the PW database) together with the $database API. This might give the fastest performance.

[AAD Web Team 84]

What's the best practice for using an external database – say, a MySQL database that lives on the same server as the ProcessWire database, but is separate from it? I had a look at $database in the API, but it looks as if it understandably just refers to the current ProcessWire database.

[flydev 👊🏻 1,736]

4 hours ago, AAD Web Team said:

What's the best practice for using an external database – say, a MySQL database that lives on the same server as the ProcessWire database, but is separate from it? I had a look at $database in the API, but it looks as if it understandably just refers to the current ProcessWire database.

$mydb = new Database('localhost', 'user', 'pass', 'my_other_dbname'); 
$result = $mydb->query("SELECT * FROM a_table"); 
[...]