what's the best approach to store large and repeatable dataset (working hours)

[Federico]

Hi there,

I would love to hear your opinion regarding the possibility to store and manage users timesheets in relation to some projects, all within PW system.

In essence, I need to find a working solution to let some users under this page tree:

- Admin -> Access -> Users

to fill a timesheet (under custom admin module), for every working day. The info I need to store in database is composed as follow:

1 - the user selects the day by picking it from a daytime inputfield -> Date

2 - then the user select an existing page located elsewhere in the system (called projects) -> Project name

3 - user adds number of working hours dedicated to that project, repeating the whole set if within the same day the projects where more than one -> Nr. of hours

 

Each PW users should be able to see only their inserted values (all days stacked in a table manner), not what other users placed from their own. So depending on user login, one might see different rows in the timesheet table.

I am thinking a sort of repeater list with sort of user permissions - all within the custom module form - but since repeaters are not suitable for unknown large dataset, I am still looking for better paths. 

What do you suggest about storing such potential infinite scalable data?

Thank you very much

fm

 

[FrancisChung]

I would represent each timesheet as a page and have the following basic structure.

Home

    Projects

    Users

    Timesheets

 

And any pages underneath Projects, Users, Timesheets represent a single instance of its parent.

Maybe you can use the repeater(s) inside each Timesheet object to represent the different working hour sets if needed.

[Federico]

Thank you for the inputs Francis

My situation is very similar to what you have narrowed down, though my major doubt is still how to store such ever increasing amount of data in database.

In particular, I am thinking to store user working hours as follow:

under page "timesheet", the custom module will generates children pages corresponding to each inputed days (even if only one single user will input a working hour for certain day, the module will generates a children page named with the current date) - so after one working week I will expect to get a structure similar to this:

Home

- Projects

-- Project 01

-- Project 02 ....

- Timesheet (all children pages contain repeaters to store user working hours)

-- ts-2017-12-02

-- ts-2017-12-03 

-- ts-2017-12-04

-- ts-2017-12-05

-- ts-2017-12-06

- Admin

-- Users

--- user 01

--- user 02 ....

 

Each timesheet children page contains a repeater to include every working hours for each users. Doing so I will limit the usage of repeaters within the same page, as this is the only parameters I can assume will stay under reasonable number (it depends only on how many users will be inputing working hours, but they can normally need just 1 repeater/user)

Do you think this is a valid approach? (referring to the Timesheet children pages corresponding to each working days, where each children pages have repeater inside them to store the user working hours)

Alternative to this approach?

much appreciated

[FrancisChung]

If the Timesheet page is limited to a day, how many entries could a user possibly input into the per timesheet repeater?  Would be less than 24 , no?
If you are not convinced of the repeater's performance, you can either

1) substitute it with page objects   OR 

2) build a site filled with mock timesheets & hours entered and try running some test queries on it and see if it's up to your expectations.

[apeisa]

I think profields table would be most suitable for your needs. https://processwire.com/api/modules/profields/table/

[Federico]

31 minutes ago, FrancisChung said:

If the Timesheet page is limited to a day, how many entries could a user possibly input into the per timesheet repeater?  Would be less than 24 , no?

this depends on the number of users inputing working hours per day, as they could be like 100 every days, resulting in a potential scenario like 100(users)x24(max hours) = 2400 repeaters items every day (so a single page could potentially hosts like 2400 repeaters). Does this sound doable?

 

16 minutes ago, apeisa said:

I think profields table would be most suitable for your needs. https://processwire.com/api/modules/profields/table/

I've tried it, very powerful, but first I have to find the most appropriate logic to store working hours.  I'll definitely give it a another try with the above logic, if no better solution will arise

 

[FrancisChung]

1 hour ago, Federico said:

this depends on the number of users inputing working hours per day, as they could be like 100 every days, resulting in a potential scenario like 100(users)x24(max hours) = 2400 repeaters items every day (so a single page could potentially hosts like 2400 repeaters). Does this sound doable?

 

3

I think it's not the number of repeaters but the number of items within a single repeater that could be the key here (I could be wrong here).
So you would have 100 repeaters containing max 24 items per repeater.

Unfortunately for you, the use cases I've personally used regarding repeaters are quite small in comparison so I'm not the best person to comment on this. 

Perhaps someone who has done something similar is kind enough to share his/her experiences with us?