Well in my software/application there will be hundreds of vars and tens of categories used for storing settings. One example is global settings category, which has vars such as site name, admin email, site default theme and so on. Another example is user registration settings, admins can enable/disable a few items/options to create a fully customizable registration system such as minimum password length, account activation requirement, security questions etc.
The software can be installed like what you do with Wordpress, Drupal/Joomla or VBulletin/IPB, it comes with an Admin Control Panel with allows site owners/admins to alter database records and site settings. The problem is, where is the ideal location to store the site settings? In the previous versions of my software the settings have been stored in mysql database, but honestly I dont know if this is the best choice to take. If settings categories are stored in separate database tables, a trip to database to grab these settings can be expensive. How about XML? One concern I have is that advanced users may be able to view the XML files, so security is a concern. Also loading and parsing XML may not be much faster than reading from database. And what about other possible options? Any thoughts?
Hi,
IMHO it is best to store such options in the database. If the options don’t change regularly then caching can eliminate those trips to to the DB. Reading out to config files is generally slower than database calls.
Other than the database connection settings 
Though XML can be used for “storage” IMHO it is primarily intended for “transport”.
Complicated database architecture might entail inneficient database calls, but I think in most cases it’s just a matter of knowing how to write complex queries. eg. SELECT UNION JOIN etc. to make the code more efficient.
That is what your application cache is for. Whenever the settings change you write them out as a php array which in turn get’s loaded on each request.
You certainly don’t want to be parsing XML/YAML files or doing database queries on every request for data that hardly ever changes.
Thanks for the advice guys, I appreciate it. I think I will stick to using database storage then, sounds like a plan. I dont know if caching will work for me. As you see, I am creating a software in a way similar to WordPress and Drupal/Joomla, the client users will mostly be running servers on shared hosts so caching will not be available. I’ve looked into Memcached and even asked some webhosts myself, looks like they are not gonna turn on memcache for shared servers for a reason. Of course the story will be a lot simpler if everyone runs a VPS or even dedicated servers, but reality is quite different…
Worpress and Drupal both utilize caches even on shared hosts. Just talking about some generated files. Nothing fancy.
I see, how is this accomplished though?
There are different approaches and it’s hard to condense things down.
Consider reading: http://symfony.com/doc/current/components/config/index.html
There is a section in there on caching.
Consider also just installing the default Symfony 2 application and then poking around in the app/cache directory.
I wouldn’t worry about trips to the database. In MySQL connecting is very cheap and if your are doing simple lookups by primary key (which is what I understand you will be doing here) then the queries will be very fast. Also, it looks like these settings will not be changing very often (I mean, not many times per minute) - then you can enable MySQL’s query cache and this will make your trips extremely fast. MySQL with query cache enabled performs very well for read-only scenarios for data that don’t change frequently - and the advantage is that you don’t have to manage the cache at all in your application apart from enabling it once. I wouldn’t bother with creating additional file-based cache layers since they could be more expensive than that. XML would be the slowest of all.
Thanks for the advices guys, I will see what I can do about database caching. I did not know XML was that slow though, guess I should refrain from the idea of using it then.