5

We have got a project to build an ERP system for one of the largest garment industry of Bangladesh.

They have around 20,000 employees and about 10% of them get out/in every month. We are a small company with 5 PHP developers and don't have much experience with such a large project. We have developed different small/medium scale projects previously with Codeigniter/Zend Framework and MySQL database.

For this project we decided to go with Yii framework and MySQL or PostgreSQL. There will be about 1 million database query every day. Now my question is can MySQL/PostgreSQL handle this load or is there a better alternative? Is it ok to do it with Yii framework or there have a better PHP framework for this kind of application? We have got only 5 months to build the payroll and employee management modules.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ali Hasan Imam
  • 353
  • 1
  • 4
  • 11
  • 3
    First off congrats on getting your project. Secondly, there is no reason why it shouldn't be able to cope. You just need the right infrastructure in place. Also right from the start make sure you are making use of all tools available. Caching, benchmarking etc. etc... – martynthewolf May 23 '11 at 10:14
  • 3
    By the way, your English isn't bad at all. ;) – Koraktor May 23 '11 at 11:52
  • What about using SQL Server as DBMS over PostgreSQL? – Ali Hasan Imam May 24 '11 at 14:56

5 Answers5

4

For one thing, consider using PostgreSQL rather than MySQL. You're going to be dealing with mission-critical data and, in general, you'll appreciate that:

  • You will have access to window functions (useful for reports), with statements, and a much more robust query planner.
  • You will have extra data types, namely geometry types which can be used to optimize date-range overlap related queries.
  • You will have access to full text search functionality without needing to use an engine (MyISAM) which is prone to data corruption.
  • You will have more options to implement DB replication (some of which are built-in).

With respect to scalability, be wary that scalability != performance. The latter is about making individual requests faster; the former is about being able to handle massive quantities of simultaneous requests, and often comes with a slight hit to the latter.

For the PHP framework, I've never used Yii personally, so I do not know how well it scales. But I'm quite certain that Symfony2 (or Symfony, if you're not into using beta software) will scale nicely: its key devs work in a web-agency whose main customers are mid- to large-sized organizations.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Great answer! Just for some references, Symfony has been used for big sites such as dailymotion, yahoo answers etc. Nice suggestion. – Flukey May 23 '11 at 13:19
  • Thanks for reply. I want to get one more suggestion about database. Is it OK to use any ORM tool to handle database or manual queries will be better for performance. – Ali Hasan Imam May 24 '11 at 05:33
  • 1
    For typical queries, you'll get faster development times with an ORM like Doctrine, at the cost of a slight performance hit because these tend to add unneeded (left) join clauses. (This is less true in PostgreSQL 9.0 and Doctrine 2.0, though: the former was taught to ignore a lot of the seasoning added by ORMs, and the latter was taught to introduce a lot less of it in the first place.) For report queries, nothing will beat a properly optimized plain SQL query -- which Doctrine will let you turn into objects. – Denis de Bernardy May 24 '11 at 05:54
  • I have implemented yii for a slightly smaller scale, in the order of hundreds of thousands hits/queries per day, and it performs just fine, but I didn't use Yii's activerecord model, opting to use parsed sql queries, I think doctrine is also a better alternative than yii's activerecord model. [edit] oh and cache those static resources, it really really helps :D – ZaQ May 24 '11 at 09:49
1

I think, Yii will work fine with (relatively) large amount of data. I'm using Yii to manage 1.3 million records, some thausend updates a day and some thousand querys a day on an small virtual host with an amazing performance.

If your database can handle this data, your Yii application will also handle that.

Your choice of the database will be an important point. So @Denis said some important thinks. By using MySQL probably you have to explore / determined the right storage-engine for your needs.

But, there are some points, which i realized by creating an growing project with Yii. You should think about those things:

-Yii is an young framework: new technologies (like ajax) are supported, but in some special cases it's a bit immature: it's very easy to generate an basic application in a cuple of hours. Problem could be occur by special situation and requirements. Example: they have an nice validation-mechanism for user inputs(HTML Forms). But until Yii 1.1.6 that doesn't work with HTML Checkboxes, since Yii 1.1.7, Checkboxes are supported by default, but no groups of checkboxes. An other problem: Yii alway uses an table alias, which is always "t". That could be a problem! Sometimes you can define that alias, sometimes not (which is inconsistent). If you like to lock a couple of tables in MySql, you ran into a problem, because Yii calls every table with the same alias "t". So you are unable to loot the tables in MySql by tablename and it's also impossible to lock a couple of tables, which called by the same alias. -> those are specific problems, you can solve them, by writing pure PHP (not using Yii functionality) What I'm trying to say: the framework will not be helpful in very case, but in mostly.

-Yii is easy to extend. It's easy to add own extensions or functionality. So lot's of those "small problems" can be solved be writing own extensions, widgets or by overriding methods.

-Yii supports PHP 5.2. Yii is compatible with 5.3 but (Yii runs on 5.3 - i'm still using it since yesterday, it work's) but doesn't support new features from 5.3 (maybe you need one?) PHP5.3 will be (maybe) supported with Yii 2.0 - in a distance future (2012)

-Yii has a small (but very good) community.

-there is no professional support (you can post bugs in hope, anybody will fix it - or you will fix it yourself)

-Yii is OO PHP. Think about that by handling with Data-Objects. It's possible to load large amount of data into Data-objects. But keep in mind, that your application server have enough RAM (but that's not a Yii specific thing)

At all: i like Yii an if your application is not to complex, you will have a lot of fun an an nice and powerful application at the end.

The Bndr
  • 13,204
  • 16
  • 68
  • 107
  • I think hardware will be not a problem at all. The application will be run on a dedicated server with Quad Core Xenon Processor and 16GB of RAM. We selected Yii framework for its performance reason and feature for built in Ajax support. Another thing to add in Yii can I make custom query and then convert it to a Object Model like Doctrine? Otherwise we decided to go with Zend as we have previous experience with it. – Ali Hasan Imam May 24 '11 at 14:14
  • I'm not familiar with Doctrine. But after reading that -> http://en.wikipedia.org/wiki/Doctrine_%28PHP%29 i think, Yii is also supporting that. Look at this: http://www.yiiframework.com/doc/guide/1.1/en/database.ar With CActiverecords you are able to fill the query-result into an object. It's also possible to generate CactiveDataProvider-objects from CActiveRecords, which is very useful for publishing the data in an attractive way. DAO is also possible: http://www.yiiframework.com/doc/guide/1.1/en/database.dao This Yii guide is an very helpful tutorial for your first steps on Yii! – The Bndr May 24 '11 at 14:31
1

I think you might be asking the wrong question, though.

You have five months to build an ERP system. The primary concerns should be:

  • security. You're dealing with money and personal details.
  • reliability. Uptime is probably a big deal (at least during working hours)
  • consistency. You don't want to risk losing data or corrupting data
  • developer productivity. Five months is not much time do build what you describe
  • maintainability. Sounds like this is a core enterprise asset, with a lifetime of years - it's likely to require maintenance and extension in the future.
  • scalability. You need to support tens of thousands of workers, each with many time cards, pay roll runs etc.
  • performance. You want the application to be responsive.

I would query whether performance is an absolute priority - it shouldn't be slow, but many ERP systems are a bit sluggish. Performance optimizations often mean trading off other priorities - for instance, an ORM system improves developer productivity, but can be slower than hand-crafted SQL.

As for scalability - as long as you have a reasonably designed schema, I don't think 20K employees is much of a challenge to any modern RDBMS on decent hardware.

So, if I were you, I'd probably go with PostgreSQL, for the reasons Denis mentions. Never used Yii, but it seems perfectly reasonable. I would use ORM until you find a situation where the performance really is unacceptable.

Critically, I would put together a testing framework which allows you to monitor performance and scalability during the development cycle (I use JMeter for this), and only make performance optimizations if you really have to. Sacrificing all the other things - especially productivity and maintainability - in the name of performance before you know you have a problem tends to create over-complex solutions, and they in turn tend to have more security issues and maintenance challenges.

halfer
  • 19,824
  • 17
  • 99
  • 186
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • Actually we have got 18 months for completing the full ERP. The first phase is payroll and employee management which need to complete in 5 months. As for database we have decided to go with PostgreSQL but we have a chance to use SQL Server 2008 as DBMS. Will it be a better choice than PostgreSQL? – Ali Hasan Imam May 24 '11 at 14:41
  • I've not worked with PHP conbined with MS SQL for years - but at the time, the drivers were very poor, and it felt a lot slower than MySQL. It also meant we needed two administrators, as the MS SQL guy didn't want to touch Linux/PHP, and the Linux guy hated Windows. In terms of capabilities, scalability and performance, I don't think either solution will be critically better than the other for you - developer productivity (i.e. familiarity with the platform) is a far bigger risk. – Neville Kuyt May 24 '11 at 15:03
0

Just to add , Yii scales very nicely in both directions (ie functionality addition using new modules etc and is one of the fastest php frameworks when it comes to performance ). The only drawback I can see with Yii is that it has lesser user base so a bit lesser support than some other frameworks, but this is changing fast. The best part of Yii is the gii based code generation which helps you get started really quickly once you get used to it.

arkoak
  • 2,437
  • 21
  • 35
0

Yii is very flexible, light and easy to learn PHP framework.

RusAlex
  • 8,245
  • 6
  • 36
  • 44