Hello!

I hope you're having a good day.

There is a functional website available, which was initially developed as a personal hobby project. It is written in PHP and the data is stored in text files. However, it has become apparent that a database should be implemented for better usability, especially for features like user registration and a guestbook.

Now the question arises - which database should be chosen?

From my understanding, one of the advantages of SQLite is its simplicity when it comes to making backups and transferring them to another hosting service. You simply need to copy the database file.

Therefore, I seek advice from professionals - would SQLite provide sufficient capabilities and performance, considering that the site is expected to grow and additional features like a guestbook will be implemented? Or would it be better to opt for MySQL instead?

I believe this decision holds crucial significance for the future development of the website, and your input would be greatly appreciated.

Thank you in advance for any insights you may provide!

I would recommend using MySQL instead. While SQLite may suffice for now, as the site grows, it may eventually reach the limits of what SQLite can handle, necessitating another migration. On the other hand, MySQL offers a simple backup process through phpMyAdmin, allowing you to easily create and restore backups with just a few clicks.

Additionally, hosting companies typically perform daily backups (sometimes even more frequently), or provide you with the option to initiate backups from the admin panel, often with the ability to set up automated backups.

Furthermore, during the initial stage of content filling, you are unlikely to notice any significant performance differences between the two databases.

Considering these factors, MySQL appears to be the more reliable and scalable choice for your website's long-term growth and maintenance.

The impact of buffering will only be noticeable when handling large requests, whereas for simple queries, any differences will be imperceptible.

I understand that the recommendation I provided was based on the specific requirements of your project. Of course, one could argue in favor of using MySQL or even Oracle. However, it would be wasteful in terms of both financial resources and computational power to opt for MySQL if the projected workload does not justify it.

If your website is not expected to experience high traffic or a significant increase in users, then it seems that relying on the database alone may not address the issues that could arise from an influx of users. Regardless of the database choice, if the code itself is poorly written, the effectiveness of the database will be limited. Please note that I mean no offense with this statement.

In my opinion, rather than reinventing the wheel, it may be more beneficial to focus on addressing real challenges. For the current requirements, SQLite should be sufficient. However, if the project does grow significantly, opting for a ready-made content management system might be a wiser decision. This way, you can leverage the functionality and scalability offered by existing frameworks without having to develop everything from scratch.

If the anticipated workload is minimal, SQLite should suffice for your needs. Although configuring it for multithreaded mode may require some time, the process itself is not overly complicated.

However, if you anticipate a high workload, it would be more advisable to consider using MySQL or PostgreSQL instead.

In my personal experimentation, using SQLite on a HDD disk yielded a performance of 30 test operations per second (compared to 1000 on an SSD). Each test operation consisted of one select, one update, and one insert query.

When it comes to administering SQLite, there is a tool called Adminer, which serves as an equivalent to phpMyAdmin.

while SQLite may be suitable for smaller projects or limited workloads, the choice of database ultimately depends on the specific requirements and expected usage patterns of your application.

While SQLite has its advantages, such as simplicity in managing backups and portability, it's important to consider the long-term needs of your website, especially with the anticipated growth and additional features like a guestbook. From a web design standpoint, MySQL emerges as the more suitable choice due to its capabilities, performance, and scalability.

MySQL is renowned for its support of concurrent access, handling complex queries and transactions, and efficient management of larger datasets. As your website grows, the ability to handle increased traffic and user interactions becomes crucial, and MySQL is better equipped to manage these demands.

Moreover, MySQL seamlessly integrates with PHP, offering a wide array of tools, resources, and community support. This integration ensures smoother development and maintenance processes, enabling you to implement new features and functionalities with ease.
By opting for MySQL now, you are laying a strong foundation for your website's future growth. This decision will empower your site to accommodate expanding user bases, incorporate additional features seamlessly, and maintain high performance even as the site evolves.

Quote from: NoelJones on Apr 05, 2023, 06:42 AMHello!

I hope you're having a good day.

There is a functional website available, which was initially developed as a personal hobby project. It is written in PHP and the data is stored in text files. However, it has become apparent that a database should be implemented for better usability, especially for features like user registration and a guestbook.

Now the question arises - which database should be chosen?

From my understanding, one of the advantages of SQLite is its simplicity when it comes to making backups and transferring them to another hosting service. You simply need to copy the database file.

Therefore, I seek advice from professionals - would SQLite provide sufficient capabilities and performance, considering that the site is expected to grow and additional features like a guestbook will be implemented? Or would it be better to opt for MySQL instead?

I believe this decision holds crucial significance for the future development of the website, and your input would be greatly appreciated.

Thank you in advance for any insights you may provide!

Not ideal for very large datasets or high traffic websites. Performance can degrade as data grows.