CMS Made Simple Forums

I am looking into writing a module to record timesheets for employees (and process them via export to external software). Each workday would create a record, per employee. If I only record the actual days worked, I have about 200 records per employee, per year. A company of 50 employees would therefore have around 10,000 records per year. This data should be kept for at least 7 years => 70,000 records, unless I export/save the data in between. As the company may also grow, I should expect at least 100,000. Is this pushing CMSMS / mySQL to the limit? Alternative ideas also welcome. cheers, Mario

rotezecke wrote: ↑Mon Nov 02, 2020 12:59 am Is this pushing CMSMS / mySQL to the limit?

Not even close to!

Although CMSMS provides an abstraction layer to access the database, it doesn't interfere with it at all and you can always access it directly if needed for some specific operations that the abstraction layer may be more of a hassle than a boon.
Regarding MySQL, and MariaDB for that matter, the limits are a bit more theoretical... on the order of the Terabytes!

A few resources from the docs on MySQL limitations:

http://dev.mysql.com/doc/refman/5.6/en/database-count-limit.html wrote:Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html wrote:There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.

http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html wrote:Every table (regardless of storage engine) has a maximum row size of 65,535 bytes.

So in practical terms, in a shared hosting environment, you'll hit server specific limits before you are close to hit any of those limits. And you'r not likely to get near them with say, 1 million records, provided there won't be weird BLOB fields.
For instance, LISE has been working with multiple instances with near 200k records in some installs, and although it is not the champion of efficiency (LISE uses a database model that is not the best one for this kind of uses, at least not on it's current form) it holds its own easily depending a bit on what is requested of it.
So you can rely on CMSMS and mysql for most practical uses, just make sure you have a good database table design yo start with when you plan your module.

A quick note:

http://nickduncan.co.za/maximum-mysql-database-size/ wrote:Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

HTH.

Thanks again for yet another detailed response, JoMorg. At this point, you put more work into this module than I have. :)
While the scope of the project isn't fully clear yet, I feel more confident it will work regardless of what they throw at me.
Very good point about shared hosting, though. That's already a known bottleneck. Cheers, Mario