Jonathan Levin's Axioms

Business Intelligence for the People

2009-06-17T01:33:00.007+08:00

Business intelligence has been talked about for quite a while. Even today, while companies are looking to make budget cuts, some experts are saying that BI can be used to beat the recession.

When I hear about BI systems, the first thing that comes to my mind is a huge and expensive system that has very powerful servers, that sucks data from many sources and runs some intensive and even more expensive reporting suite. Since I had been involved in projects to set those systems up, I know that it can probably take around a year to complete.

So everyone is in fact thinking about saving money yet still being competitive and aggressive in the market. On that subject, there are very popular and stable open source BI suites and similar products (kickefire, scaledb) to achieve both those goals. Before you deploy some “black magic BI”, because that’s what the big companies are doing, there are some simple things you can do to get valuable information from your data.

Assuming your data is in one place, you can create reports using standard SQL that will benefit your company and make them more competitive. To help with that, I have compiled a list of relatively generic reports that most companies might/should be interested in. There is a good chance that after making these reports, the management will see that you actually have some business knowledge and might ask you for more specific reports. If that is the case, then consider the information you have delivered to be a success.

Sales

What is the total number of placed orders?
What is the total number of orders that were shipped or invoiced? - When a customer places an order it is not always the case that the products are in stock.
What is the total amount of money from placed orders? – Sum of order value. This can be with or without shipping costs, depends on the management.
What is the total amount of money from orders that were shipped or invoiced?
What is the total number of returned/cancelled orders?
What is the total amount of money that was lost from returned or cancelled orders (or credit notes)?

These reports should be for specific period of time. For example 1,2,6 and 12 months or 1,7,14,21 and 28 days.
You can compare this month to last month’s results or this month to the same month a year ago.

These reports can also be grouped by:

Product types or product ranges
Geographical areas (city, country or continent)
Customer matrix (teenagers, elderly, soccer moms, university students..).

Products (Best Sellers)

Which products sell the most quantity?
Which products generate the most revenue? – (product price * quantity)
Which products generate the most profit? – ((product price – product cost) * quantity) – To make your business more profitable, you should invest your energy in the 20% of the products that generate 80% of the profits.

Customers

How many new accounts/customers do we get (each day, week, month, half year or year)?
Who are our best customers – that buy the most from us? - The top 20% that make 80% of the profits.
What is our attrition rate – the numbers of accounts/customers we lose (each day, week, month, half year or year)? - If your attrition rate is high, the sales team needs to find a lot of new people just to keep things the same.
What is the customer satisfaction? – You will probably get this from surveys or customer feedback.
What are the top reasons for complaints?
How many existing customers referred new customers to your company? – You might need to setup something to collect this data. If you send your customers to do your marketing for you, you can save a lot of money.

Website
(Some of these reports can be handled by a separate application like google analytics)

Which product pages get the most views?
Which news/blog articles get the most views?
How many minutes does the user spend on the website?
What is the percentage of abandoned shopping carts?
What is the average amount of time a customer spends in the shopping cart/session?
Most important – What is the conversion rate of the visitors coming to the site and the people who purchase a product?
Which landing pages are the most viewed?
Which landing pages have the highest conversion rate?

Final Words
I genuinely hope that this list of reports will help you influence your business and your position in the company for the better.

I would very much like to welcome ideas for new reports or comments on current ones if they are unclear. Your feedback is appreciated.

Tired of Youtube? Try the hacked version

2009-06-14T21:01:00.008+08:00

Tired of not watching the latest music videos on youtube like the way you used to before? Frustrated at the live performance or unplugged version of the song you wanted to hear (I actually like it that way, but anyway…)? Well do I have a solution for you!

China has their own version of youtube called YouKu.

We all know for a long time now that in China, they copy everything and have very little or no respect for copyright laws. This sometimes upsets us (check out StackOverFlow vs CNprog), but here is a chance for us to get something back.

Go there and put in the search a name of a particularly famous/infamous blonde singer and see all the lost or missing music videos from youtube.

If you are lucky, you might even see full movies on it.

However, it might be “a bit” slower then youtube is. Sorry.

Caching Business Logic in the Database

2009-05-31T06:19:00.004+08:00

This is a presentation I gave at the Cloud Expo Europe in London on the 21st of May 2009.

I speak about:

Caching the results of processed data - data that had business logic applied to it - to the database to be re-used later.

Caching Business Logic in the Database

I Will be Speaking at the Cloud Computing Expo

2009-05-07T22:45:00.003+08:00

As the title says, I will be speaking at the Cloud Computing Expo.

And while I have spoken before to a bunch of people before, this is by far, a lot bigger.

The topics I was thinking of talking about was a piece from my previous presentation which would be about speeding up MySQL for a web site developers.

I have edited those parts of the presentation and uploaded then to youtube.

They can be found here and here.

What I'd like to talk about is a mixture of the posts I had about Storing Conditions and Using Business Rules in MySQL. The idea, as a friend of mine put it is "Caching of Logic" in the database in order to save reprocessing data.

I hope that I am not very vague and I guess that I will need to make it clearer. But for now, I will start preparing the presentation (and I am quite excited about it).

Slave Replication Errors

2009-04-17T09:00:00.003+08:00

I had a problem with slave replication, every now and then we would get an error which manually had to be resolved

We used Set Global sql_slave_skip_counter = 1; start slave;

You might also get some of those when you set up a new slave using a mysqldump. If from the time you started the mysqldump and by the time it finished, some lines were added to the bin logs or you started it at the wrong position, then you would get some duplication errors.

While it looks concerning, don’t panic. You can fix it by skipping the lines that are duplicates, although it’s not an ideal situation.

Now if you want to completely avoid errors, or specifically duplicates and you are sure that the odd ones that come along are not important, then why not try log-slave-errors=1062 . Even better, you can set it to “all” and be completely oblivious to any problem that comes along.

Every now and then you can check the log errors and if there are too many problems, get a new mysqldump to re-sync the slave.

Speeding Up MySQL for Website Developers

2009-03-15T22:41:00.006+08:00

I gave a presentation for PHP West Midlands (UK) about how to speed up MySQL for people who are primarily website developers.

Now the audio for the presentation could have been better. I speak very fast for some reason. I am foreign, so I have an accent. And to top it all off, I have some mistakes here and there.

If this still hasn't discouraged you, please watch the video

Follow Up - Question of Speed and Technological Progress

2009-03-07T08:20:00.014+08:00

Well, the servers we bought (Dell R200) were in fact very cheap.

We were using 2 of them and they could not stack up to 4-5 year old servers (Dell 1750 and 1850) which have SCSI hard disks.

I added a 4 year old Dell 1850 with SCSI disks next to the 2 R200's which were all connected to a separate server running pound load balancer. The total number of maximum concurrent connections tripled.

The Dell R200's cannot actually take SCSI/SAS drives (unless you go buy a proper controller). By default, they can only take Nearline-SAS which I heard is not something you would want for a web server.

I would have to say that I am putting the blame on the SATA drives. I would have thought that by now, SATA drives would be as fast as old SCSI drives, but I guess not.

Apart from that, I am still surprised that the new R200's with EIGHT Gbs of ram could not keep up (not even close) with much older servers.

What I'm looking into now is using squid for web accelerating. There is a good article explaining how to install it.

While I can plainly see that the servers we chose were not amazingly fast out of the box, I'd like to believe with some tweaking and using more of the memory, they could get close to the older servers.

The final point is, that it costs man-hours to do any tweaking to the new servers. If you calculate the cost of the man-hours you would be better off just buying a faster server.

How to Move Your Web Server With No Downtime

2009-03-04T07:04:00.006+08:00

How do you move your web servers to another location with no downtime?

The answer: the magic of MySQL Master-Master Replication

So, we are in the process of moving our web servers to another location. As usual, we would prefer to move them without anyone really noticing. What we have done is set up a skeleton of web servers in the new location as well as one database server.

Now the trick is, to move without anyone knowing it AND that everything else continues working. In this example, the original location has servers that query the database if there are any new orders and process them. So it is important to keep all the parts of the system running by continually updating the database in the original location. So in this example, if you point your DNS at the new web servers and don’t update the old database, you will basically lose money.

So, what do you do?

Well firstly, you should set up a secure and stable connection between the two locations so that the databases can communicate with one another. An SSH tunnel, will do the trick. You can set them up on the actual database server on a certain port, and it will seamlessly connect to the other database server as if it’s on your local network.

Secondly, you set up a Master/Master Active/Passive replication. This is a replication set up which is usually used for high-availability. You can also use Active/Active replication for load balancing if you take into consideration that auto-incrementing numbers would conflict on both servers. There is a solution for that as well.

On the day of the move, tell your web servers from the original location to query the database in the new location through the SSH tunnel. This will slow them down, but it should be only for a few moments.

Change the DNS to point to the new web servers and that they are pointing to the new database server.

Now you have your web servers in your new location and all the other servers and services running from the updated database in your old location.

All that’s left is to move the other servers and complete the entire move.

Job well done! You have completed the move with (hopefully) no downtime and no orders were lost. Your boss(es) will definitely be pleased with you.

A Question of Speed and Technological Progress

2009-03-01T09:41:00.008+08:00

Follow up post to this one

At my company, we have some really old web servers that host our website. They are from late 2004 and early 2005. Now, at the time, I am sure they were quite expensive.

Recently, we wanted to move to newer web servers and we bought the basic Dell R200 rack servers. Now, arguably they are quite cheap, but thinking about it they will definitely be faster then 4 year old servers, right?

Well, that’s what my predecessor thought when he bought those servers and I myself didn’t think about it till we wanted to start using them.

We noticed that the new servers are at least twice as slow as old servers and that just completely baffled us. No one saw it coming. I mean, the old servers are optimized and tweaked and the new servers just have an un-tweaked but clean install of Ubuntu server with apache.

So what’s going on exactly? How is it that when hardware is so cheap and supposedly so fast, that we now have to spend some time going into configurations, messing around with things, benchmarking and basically spending man hours on something that is supposed to be quite basic. I mean, I even used my cure-all method and threw some memory at the new servers to get them to 8Gb and it still didn’t work.

Anyway, so now we have to start spending time on speeding up the servers. Things that have been suggested were:

Removing un-needed modules or parts from the linux kernel and recompiling it. Although, I’m not too thrilled about that.
Tweaking Apache Config
And my suggestion which was put everything in memory with TMPFS. Again, I like memory, I’m from a MySQL background.

From my experience, PHP isn’t usually the bottleneck. So I need to look at the whole puzzle to see where the bottlenecks are.

For my part, I have looked at the MySQL server and we also monitor it with Nagios. I am not seeing any particular slowness from it and Nagios isn’t telling me that its being overused, but I guess I could still run some benchmarks on it.

Anyway, it’s a big mystery to us why it is as slow as it is compared to some 4 year old servers. Granted, the servers we have now are quite inexpensive, but we are still very surprised.

Conclusion

Moore`s law doesn't apply to web servers and cheap hardware will always be cheap.

I’ll leave some links that I found recently and we will try to use them to speed up the web servers.

Slashdot Effect

http://www.linuxformat.co.uk/pdfs/download.php?PDF=LXF97.slashdot.pdf

Apache Tweaks

http://www.askapache.com/htaccess/apache-web-server-speed-configuration-hacks.html

TMPFS – Linux Memory Partition

http://www.howtoforge.com/storing-files-directories-in-memory-with-tmpfs

http://kevin.vanzonneveld.net/techblog/article/create_turbocharged_storage_using_tmpfs/

Follow up post to this one

What the ‘Microsoft’?!

2009-01-28T08:47:00.008+08:00

I’ve had about enough of Microsoft and its approach to help small businesses develop application. The .NET platform is FAR FAR from ‘working everywhere’ and being compatible to everything. Let me write down some points to what I mean.

Windows Applications that are developed in Visual Studio.NET by SMBs usually (almost always):

Do not work on other machines, even when its .NET. Don’t assume a .NET application will work on every machine. You need an installation of visual studio for each machine you want your application to work on.
Do not work with Crystal Reports. It might work “ok” on your computer, but if/when it doesn’t print on someone else’s machine, try to find some support.
Stop working when you update windows. Update at your own risk
Randomly disconnects from the network or has DNS problems. From the words of my system administrator “its being windows”.
Randomly doesn’t connect to the database. See above.
Locks up the database with all the open connections. That’s what happens when you distribute fat clients to everyone.
Crashes when some windows services mysteriously stop working.
Have issues when being deployed and make you click 10 ‘OK’ buttons just to open it. I do not know why, but someone previously decided to have a shared network drive with the .NET files on it, so that we can update one place and everyone goes there to run it (every single time). But try as they might, they still needed to put some config files on each machine.
Have a low learning curve which lets idiots make decisions on software architecture which afterwards are too complicated to change.

My personal opinion is that the Microsoft lock-in, is the problem. It starts off with

Oh yes, its so comfy and easy using Excel sheets
Oh.. lets write some functions using my macros
Now lets try some VBA/VB6
Hmm… we need a database… oh.. lets use Access.
(A year goes by….)
Hmm.. we need something more serious, we have more people using our system…. lets call someone and ask what we need…
Salesman: “yes, what you need is first of all 2 Windows servers, one with a domain controller/Active Directory and one with MS SQL database. Then you will need lots of CALs (licenses). If you are already there, you might as well use Exchange too. Then you need 2-3 copies of Visual Studio.NET team edition for your small development team. You know what?.. I think you will need a BizTalk server as well, just to be on the safe side. Oh.. and finally, installation and maintenance fees will come to ££££££££££££££££££!”.
Managing Director *gulp*.

Maybe nowadays this scenario may not be so common and more people would be happy cutting down costs. Perhaps by using MySQL instead of MS SQL. However, then you have interfered with the natural order of the way Microsoft wants things to be.

I don’t know, obviously this is a rant because I’m frustrated about some things. I guess I am glad I decided to do something about it.

Caching and Actually Speeding Things Up

2009-01-19T01:50:00.008+08:00

I've ran into many people throughout my career that were under the impression that caching is a cure for cancer, world hunger and global warming. I myself often fall pray to the "just add more memory" approach for solving problems.

Some 5 years ago, while I was working with ASP classic and VB 6 (yes, you are allowed to chuckle now) on my online movie collection, I came up with the idea that nothing actually beats a flat and pre-processed HTML file for speed. I mean, this may seem obvious and no matter how many times I tried to add cache and do all sorts of tricks, nothing would beat a static file. The problem here is that when I suggest this sort of solution, people would almost always answer that "well, we have caching anyway" and that this sort of solution would actually be going backwards.

Now granted, you cannot use static files for most dynamic pages (static != dynamic) and maybe this would never be a real long-term solution, however lets just consider these benchmarks for PHP and Ruby Frameworks. No matter how many different types of caches you apply to your framework, you will never actually beat HTML files or baseline PHP files for that matter. But the point is, that going back to basics beats frameworks by an order of magnitude.

This reminded me of something I wrote a loooong time ago about event-driven publishing, when something is updated in the database, the webpage cache of a website gets regenerated.

This also reminded me about my thoughts about storing conditions in database tables, where you can save pre-processed results to the database to save you re-processing them.

Conclusion

Well, not so much a conclusion, but an option. Consider making the parts of your website that do not change very often, into static files if you are very concerned about speed. Have your system generate these static files every time they need to be updated.

Developing Internal Business Applications with PHP

2009-01-12T21:44:00.003+08:00

Recently, my company has decided to go full (or mostly) open source by moving away from Microsoft technologies. This is a particularly exciting venture to be part of, but in order to explain what I mean, I need to go back a bit and explain the history of the company.

History

Since its inception, the company has been a traditional LAMP stack. Over time, some internal business applications were needed to be developed. The initial platform to solve this requirement was Microsoft Office and specifically Access database and some Excel sheets. Later on, more and more VBA programming was added to the Access database. At some stage, a decision was made to move to VB.NET and port the VBA/VB6 code to it.
So far, this story is not that unique and I am sure many of you reading this can either relate to it or know of examples from other places.

There were issues moving to VB.NET from the start. It took over a year to finally move to it with many complications and a lot of resources invested in it during that time. After some time, the resources depleted and 1 developer was left to maintain the system.
With time, the application stagnated and the 1 developer could not keep up with the bugs and requirements for new features.

The Present

At this stage I came into the company and I decided that some serious work had to be done to improve the internal business application. I had made a plan to fix and improve the existing code base but saw that it would take a considerable amount of time and energy as well as expertise that we did not have.
At this point, I took a look at other parts of the company. The company has a very good history with open source products. The relatively new PHP team was accomplishing quite a lot of things. All the servers are on various flavours of Linux and maintained well.

The only parts that were holding the company back were closed source technologies. Specifically, incompatibilities between 3rd party applications, printers and forced networking standards that come with closed source. The cost for .NET developers was also an issue.
It seemed to me that the culture of the company is more aligned with open-source and ultimately, that would be the direction the company should follow. The only problem was that, because of initial convenient and easy-to-use application, the company was locked in to a now costly and constricted system.

The system that was chosen was PHP. From one point of view, PHP was the obvious choice, considering the expertise the company has. From another point of view, selecting a web-based technology to replace fat clients with rich GUI, isn’t the most obvious choice.
Considering all the different points (and after being grilled by the executives a bit), the decision was made mainly because of our strong history, achievements and expertise with PHP and Linux. The difficulties that PHP might have to replace the VB.NET system were researched and solutions were offered.

The Official Reasons to Move to PHP from VB.NET

To cut down costs and unify technical support expertise. It is difficult and costly to support multiple systems technologies.
To reduce complexity and speed up development time. The company has had a lot of success delivering products and features with PHP and believes that this success can be replicated for the internal business application.

Full Steam Ahead

When making this system it is very important to make sure that the system will be very stable, maintainable and flexible for future changes. It would need to prove that it can overcome the difficulties that the old system had troubles facing. After that, development speed is crucial as all new features will have to be put on hold until all the system (or at least some parts of it) is rewritten.

So the important factors were the quality and flexibility of the code as well as speed of development. I decided that in order to achieve these goals, we would need to use frameworks, tools, add-ons and coding standards. The idea is to leverage existing open-source products and best practices to save time and ensure quality. It is also important to make sure that by using the same tools and following the same coding standards, the code will be maintainable for many years to come.

The List of Technologies

I have compiled a detailed list of technologies, open-source products, frameworks, tools and coding standards we plan to use to get this project done properly. All of these are supposed to lay the foundation for a rigid and structured development process.

LAMP Stack - PHP 5, MySQL 5.1 – for improved replication and partitioning, Ubuntu Server and Apache Web Server
Zend framework – This is something the company has chosen a long time ago and it’s something we plan to continue with.
APC – PHP accelerating to speed up bootstrapping.
Memcached – To speed up database interactions. We came to the conclusion that memcached sped our whole system by a factor of 40.
Doctrine ORM – I didn’t want us to spend a second of a developers time on mapping database tables to classes, so I wanted an ORM. Doctrine seemed to have a better website then Propel, so we chose that. So it’s literally a case of judging a book by its cover.
YahooUI Javascript Framework – After my developers being biased to jQuery and I was biased for ExtJS, we decided to choose YahooUI as it was somewhat close to the rich GUI that our VB.NET uses now. We chose this framework to try to keep javascript development to as low as possible by using already made widgets and libraries from the framework.
PHPUnit – This is one of the important parts of the foundation we need to start building the new system. If we cannot have some guarantee that the system is stable then what is the point of the whole thing.
phpUnderControl with CruiseControl (Continuous integration server) – “Continuous integration describes a set of software engineering practices that speed up the delivery of software by decreasing integration times”. phpUnderControl uses PHPUnit, PHPDocumentor, PMD, PHP_CodeSniffer and CheckStyle.
Subversion SCM – We obviously, need source control management and we are already familiar with subversion. We had considered Git, but didn’t choose it in the end.
Zend Coding Style – The development team decided that in order to organize and standardize their code, they want to use the Zend Coding style. I also understood that it will later help with phpUnderControl with its style checking.
Scrum with ScrumWorks – We use Scrum and we find it very effective. The application we use for it is ScrumWorks Basic. Even though we don’t think it’s the best one, it works for most cases and is quite convenient.
Selenium with PHPUnit extension – for front-end testing. Selenium can test front end pages/forms and javascript functionality. Selenium can be called from PHPUnit which will fit with the automated testing suite.
Acceptance Testing for our User Stories – This is a standard we liked. We like to specify with the user stories the acceptance test or success criteria for this user story to be considered done.
Fat-Models, Skinny Controllers – A standard we are all in agreement is a very good practice.
PHPUnit TestDox for documentation - I wanted the tests to be the actual documentation. All the requirements and behaviors of the system should be the actual tests of the system. PHPUnit has a way to extract the test name and generate documentation for them. We will also use PHPDocumentor and Dokuwiki for anything else.
SOAP/REST– We will use SOAP/REST for any component that might need to be called from another location. With all the talk about SOA, I decided to just implement the minimum use of it, which is to use it when other locations need to use this functionality. This is instead of having the code in 2 places and both try to get data from the same database as the point of integration.
FireFox/Prism - We can dictate which default browser we plan to support. Prism is also quite a cool application to make our application look like… a real application.
Validations for HTML and CSS – Even though we don’t need to make our internal business application SEO friendly, it still might be a good idea to run some validation on it.
LDAP (linux) - We though it might be a good idea to have this for user logging and authentication, but the brightest idea would be that we would then know which printer is closest to that user.
Nagios for Web Page monitoring – We can monitor if the website is up and get advanced warning if something is wrong.
Log4php – a ready made logging library ported from java.

Conclusion

We are all quite pleased about the decision and look forward to start working on this project. I hope to learn as much as I can from this experience and I am sure the development team feels the same way.
I strongly believe that PHP with the addition of other open source products can deliver the same if not better results then VB.NET/ASP.NET would and I plan to put my money where my mouth is.

For writing this article, I did some research into Enterprise PHP, but did not find too many (current) articles about them. I hope to start seeing a bigger trend towards Enterprise PHP applications
I will keep bloging about the project when interesting things come up and I keep the right to update the list I made in case we decide that some parts of it are not as good as we thought

New Year's Resolution

2008-12-31T23:16:00.003+08:00

Its been a while since I blogged.. and my new year's resolution would be to blog more.

I could say that I have been very busy at work. I can't really say that I haven't been using MySQL, since I've been using it more these days than I have in my entire career.
I could say that I am noticing a trend to blog less in the MySQL community. I could be wrong about that, but I definitely don't see the same names blogging as the ones I used to see a year ago. But that could be that some people just moved on.
I could say that I have just been lazy. My blog statistics certainly reflect that.
Finally, I could say that I lost a bit of enthusiasm for blogging about MySQL. This could be that my priorities have shifted slightly, but I plan to get myself a bit more involved with database developing/administrating in the very near future.

Other things on my list for the new year is:

Re-learn the PHP that I forgot
Learn Subversion branching
Learn Git
Learn PHPUnit testing <- almost done
Learn how to use CruiseControl or PHPUnderControl
Look into ExtJS (for a Intranet Project)
Learn Ruby and/or Erlang in my free time......

Reporting SpeedUp Trick

2008-12-15T00:08:00.012+08:00

I've been asked to make some reports recently for some people in my company.

Almost all of them are quite heavy and hit the database pretty hard.

The thing is that these reports are usually generated once or twice a month, but they process a year's worth of data. Every time they are generated, they really upsets my poor production MySQL database.

I was thinking if there maybe could be a better way to just save the processed data in a table and update it every now and then.

So.. this is what I did:

I created a table that keeps the aggregated results from the report.
I added to the start of the SQL statement "INSERT INTO reports.thistable SELECT (the worlds most intensive SQL query) ;"
At this point I realised that I need a bit extra help determining which parts of the data should be updated and which is already in the table. So I added an "last_updated" column in both tables with a current_timestamp.
I changed the INSERT INTO statement to an INSERT.. ON DUPLICATE KEY so it can add to the previous amount in the aggregated table.
I also made sure that I added more primary keys to the reports table so the statement will know which fields are in fact duplicate keys.
Lastly, I added: WHERE filter the data table's timestamp column by -> (SELECT the last timestamp on the reports table). So basically, you are (in theory) only getting fresh data to update into your reports table.

This is fine for tables that only have data inserted into them. If existing rows get updated, you might need to use a trigger.

What I plan to do is to give an option to clear the whole report table if the user doesn't trust the data. However, in the future, I will limit that query from running during certain work hours to avoid upsetting the production database.

Prepared Statements Killed My Database

2008-12-11T04:15:00.002+08:00

In two separate instances, prepared statements used up all my database's connections and completely locked it. To the point that we had to restart the mysql server and in the other case, physically reset the server (not something you usually do with linux servers).

Apparently, one table had a read lock on it, which made the prepared statement wait a bit too long and it decided to flood the database connections till it would got an answer. Only the table was still locked and the database came to a screeching halt.

The last thing I saw on my screen was a bunch of the same prepared statement on my process list screen, completely taking up all the slots and then.... I lost my connection to see the process list.

I don't really understand how the perl script and php page that ran the prepared statement could be so aggressive or where was it defined to continue opening new connections till they got an answer.

This is definatly a first for me and afterwards I changed some of the tables to be more transactional, so that they wont lock.

Conclusion... well there is no conclusion. I'm not going to rule out using prepared statements, but I am going to look more into their behavior (once I find out where they are).

SSH Tunneling between databases with MySQL

2008-12-04T20:32:00.005+08:00

Dear Lazyweb

I saw recently an interesting installation at a company. This company had databases in two different locations and they established a connection using SSH tunneling.

The thing that looked very interesting to me is, they setup a dedicated machine that had a MySQL instance on it. That database was mostly empty, but it had a port open on that machine that is a "virtual" tunnel. This allowed you to connect to the databases in the second location but it seemed as if the database is local to the network.
I believe this opens up a SSH tunnel and from my first impressions, it appears to be the case. My question is, how is that setup?

MySQL Function to Change Hard-Coded Tax, Anyone?

2008-11-25T05:42:00.003+08:00

In the UK, tax prices have gone down from 17.5% to 15% .

Good news!

However, its a bit of a nightmare to change the prices on our websites. We're lucky. We don't have to reprint thousands of pamphlets that have prices for Christmas sales, since we are a primarily web-based company.

My boss said that this is like the Y2K bug, only you have 1 week to fix it.

Basically, we need to go over hundreds of pages of code for some 'very' hard-coded tax figures.

Our databases will also need to have a look at. I might need to have an all-encompassing function to look for products with tax already calculated into them and update them.

Oh well, it looks like it will be interesting.

Wish me luck.

Me ScrumMaster, You Jane?

2008-11-19T20:04:00.003+08:00

Yes, I have turned into the stereotype called a scrum-master. I haven't really done the infamous 2 day course on it to be certified, but apparently I read enough about it and practiced some of the principles to know a few things (or at least to think I know).

In my company..

In my company, we do use scrum with our development team. In most cases it works very well and the management are very happy. In some cases, where emergency support is involved, not so much, but they still like the structure of it and the fact that people commit to finishing things.

In all cases, very little testing is being applied and almost no documentation is written. I do realize that in the case of documentation, agile is suppose to cut down on those, but some form of documentation or changing some charts will be fine by me. In the case of testing, I think since I made a fuss about it, more testing has been done. But, I still think we could do much more about testing.

I spoke with my boss about it and how he looked at it was that, we had applied a process that brought in positive results and now we are at the next stage where we evaluate some of the problems that rose to the surface. Meaning, we are doing so well that we are fine tuning the process. I tend to agree with him on that. Even though technically, scrum does say that you need continuous integration testing, most places I have seen have been struggling with the testing sides of it. Oh, I did the self assessment on the nokia test and got a 5 out of 10.

Oh noes!! we must write tests now!

To be perfectly honest, its quite difficult to sell people on the idea of testing in general. Sometimes, when I mention testing, everyone thinks I am talking about moving everyone to test-driven-development. So in any case everyone is scared I am making earth-shaking changes to the whole process they are used to.

The (slight) Downsides of Tests

Tests themselves have issues that you need to maintain them. If you write a test with very specific parameters and values and then one day you change the actual behavior of the code, you will need to re-write your tests. You can avoid that by making your tests flexible for those changes, but you need to be aware and somewhat experienced in testing to do that.

Also, when you develop something new, writing tests can be much more simpler then writing tests when you are trying to fix old code. That is something I am particularly struggling with, since a lot of the work we need to do is patchwork to existing code. Just to add tests to existing code means you have to go into it, understand it and then apply some tests to it.

Conclusion

I need to study the issue more and try to apply it to my teams.
In any case, we need more testing and documentation (documentation is something I also plan to write about).

What I have decided to do is to try to do some of the following things:
1) Add certain tests as a requirement on the user stories
2) Do user stories that the whole process is to write tests for a problematic part of the code (the 20% of the code that causes 80% of the problems)
3) Do a face-off between 2 teams. One will use TDD and the other one won't. This will be a proof of concept that TDD is faster to develop then with regular development or even it is slower, only marginally slower and has the addition of all the extra tests.

Here are some questions I have asked on stackoverflow:
How do you apply Scrum to maintenance and legacy code improvements?
Scrum, but with no testing or documentation

Can't Find New Non-Windows Machines

2008-11-11T06:49:00.005+08:00

I was looking for some non-windows machines for my development team. My development team all use Ubuntu. As hard as I tried, I couldn't really find anything pre-built that didn't have windows on it.

I really don't like the idea that I will buy machines with windows on them just to have them formatted and have linux installed over them. And what really gets me is that windows will add to the price of the machine (at time when I'm trying to cut down on all my department's spendings).

I was considering to order computer parts and build the machines myself. But after getting flashbacks of my youth, trying to find the right CPU to fit the right motherboard, I decided that my time could be better spent on other things.

In the end, I found that the best option would be to buy servers for my developers instead of work-stations. I found a nice 'HP ProLiant ML115 G5 AMD Opteron 1352 Quad Core'. It was extremely cheap. It comes with 512mbs of ram, but I can add up to 8gbs of memory. Which I fully intend on doing for my own work-station, because I am trying to play around with our MySQL databases on my laptop (I copied dumps to my laptop, don't worry) and it takes forever to do the tiniest alterations.

So all in all, it is a good deal, but it's not very conventional. I was kind of hoping that Dell would do something with linux work-stations, but the only thing I could find with linux (or no OS) machines was those (cute) mini-netbooks.

Conclusion

Linux Machine with 8Gbs of Ram is much better then (>) Windows Vista machines for the same price.

Rule of Representation

2008-11-03T09:48:00.002+08:00

I have been doing some studying on the internet in my free time (not that I have too much of that nowadays) and I stumbled upon the Unix Philosophy. This is a philosophy, not method, for building programs and systems.
As I went through the rules, I found something interesting. I'll paste it here:

Rule of Representation: Fold knowledge into data, so program logic can be stupid and robust.
Even the simplest procedural logic is hard for humans to verify, but quite complex data structures are fairly easy to model and reason about. To see this, compare the expressiveness and explanatory power of a diagram of (say) a fifty-node pointer tree with a flowchart of a fifty-line program. Or, compare an array initializer expressing a conversion table with an equivalent switch statement. The difference in transparency and clarity is dramatic. See Rob Pike's Rule 5.
Data is more tractable than program logic. It follows that where you see a choice between complexity in data structures and complexity in code, choose the former. More: in evolving a design, you should actively seek ways to shift complexity from code to data.

I have read in the past many articles about how data trumps programing algorithms in many cases and I know that Google uses this principle on their searches. Its just nice to see that its been thought about and discussed in the days of when Unix was thought of.

MySQL Magazine is Out and

2008-10-24T19:21:00.004+08:00

MySQL Magazine is Out and .. I wrote an aticle on it!

The magazine looks very shiny. Go get your copy.

New Job, New Responsibilities

2008-10-22T00:00:00.001+08:00

I recently took up a position as an IT and Internet Manager at MobileFun UK.
The position itself is very interesting. The company is very aggressively trying to grow and has lots of new ideas. At the same time, I am stuck with some legacy systems that I need sort out asap. The people here are really very nice. Birmingham is also really nice and far cheaper then London for house prices.

My responsibilities include:

Maintaining the web servers
Leading the web development team
Maintaining all the servers and desktops inside the company - some are quite old.
Leading the internal application development team
The databases and the business intelligence of the company - we use MySQL 4 and 5 (I need to upgrade them)
And.. the phones for the entire company - don't know why.

So I have quite a lot of work to get done and I hope that I will have some interesting stories to blog about in the future. They will probably about fixing legacy systems...
Wish me luck.

Wikipedia - Decision Table Driven Development

2008-09-28T09:07:00.008+08:00

I have been researching Business Rules, Decision Trees and Decision Tables and how they can be used in a relational database for some time. I decided to write a Wikipedia article about a new software development methodology I call "Decision Table Driven Development".

I believe its a good idea, but then again, I am biased.

I am new to wikipedia and recently learned that I need to move my writing style away from "my opinion" to "concrete research". I already got a notice that I have 4-5 days to improve the article or it will get deleted :(

Its still a work-in-progress, but if you would like to read it, comment on it or ask questions, I would be very happy to reply. If you have written articles in wikipedia in the past and have some suggestions on what I should change, please also give me your feedback.

Edit: The Article was moved to Google Knol -> dtdd

The Database Job Situation in England

2008-09-20T18:29:00.011+08:00

Well, I'm back in "Blighty" (that's slang for Britain). I've been trying for the last 2-3 weeks to find jobs in the UK.

Since I wrote a while ago "Which Database to Choose When Looking for a Job", I wanted to continue it with some of my findings. I did a lot research on IT specific job sites and used 2 main searches, "database developer" (because that's what I want to do) and "mysql" to help me find job posts that I was interested in.

Jobs Situation for "Database Developer"

I'll describe the results for this search in percentages.

Firstly, 90% of the jobs are for MS SQL server. Those jobs usually stick to mainly developing and not administrating. In those 90% you have a good mix of junior positions(22-28k GBP per year), medium paid positions (30-45k GBP per year) and advanced positions (50-70k GBP per year).

The second 5% are for Oracle developers. Almost always it is developing and DBA'ing. The positions are usually medium positions (40-55k GBP per year) to advanced positions (60-90k GBP per year).

The third 4% are for Data Analysts. Usually this would mean 80% chance of MS SQL and the rest would be Oracle or mixed Oracle and MS SQL experience. Data Analysts are usually half ETL developers (because they need to get the data first) and half report builders. You would probably have to have some data warehouse experience. Jobs pay 30-55k GBP accordingly and mainly require you to know SQL, some statistics and a lot of experience.

The last 1% are Data Analysts again, but this time with an emphasis on Excel, VBA, Access and SQL server (a lot of people write MySQL here by mistake and it shows up on my other search :) ). This is more for businesses to analyse data or help other peole analyse data by helping them out with Excel functions. These jobs are mainly for finance sectors and quite a bit of stock trading companies and require a lot of statistics knowledge. Jobs pay 35-70k GBP per year.

Job Situation for "MySQL"

So this is 98% PHP and MySQL positions. Its almost always together. Jobs can be for junior positions (18-22k GBP per year), medium positions (25-32k GBP per year) and advanced position (35-55k GBP per year).

The second 2% are Java Developer positions with MySQL inside the wording. Jobs seem to pay 20-30k GBP per year and are usually related to website. By the way, I saw 1-2 Ruby developer job posts on this MySQL search, but that doesn't mean I would get the same if I just did a search for Ruby.

The next 1% is for Linux and Windows Administrators that need to setup MySQL and Oracle as well as maintain the system itself. Some posts are for Linux platforms only but more then half require you to know both Linux and Windows servers. Jobs pay 40-55k GBP per year.

The last 1% is for MySQL Administrators. Jobs are only for advanced positions with 3-5 years experience being a database admin for MySQL. Sometimes its a combination of Oracle and MySQL. Jobs pay 40-55k GBP per year.

Trends I Have Noticed

Clearly, if you want to develop with databases in the UK, you would make your life easier and focus on MS SQL server. This could mean focusing on

T-SQL between databases
Helping out VB.NET or C# developers
Doing ETL work with the built in packages MS offers you
Doing Reports with the built in packages
Maintaining the database and optimizing it

It seems to me that if you want to get a job working on developing in databases, MS SQL would be a really good place to start. Sectors that usually use MS SQL are finance, banks, insurance, health.. and almost all other companies in the UK apart from Web companies.

Web companies on the other hand (the large ones or digital advertising companies), use a combination of MySQL and Oracle. From the examples I have found, they use MySQL for the actual websites and then get the data (ETL) into Oracle (which they use as a data warehouse) to analyze it and make Excel reports. They said that Oracle is much better at analyzing functions. They do admit that they could "eventually" write the code they in MySQL, but the additional functionality in Oracle saves them time.

I would argue that they should just use some ETL/Reporting suite like Pentaho or Jaspersoft. Both produce Excel reports, both are more GUI-y than hand-coding SQL and probably are a better investment for their purpose.

Finally, PHP developers are expected to be "smart enough" to maintain and optimize MySQL databases. In small web companies, this is usually the standard as they may not have enough money to hire someone specifically to maintain the MySQL server(s). This problem can also be found in .NET environments, however, there are much more .NET environments that can afford to hire someone who specializes in databases.

Bibliography

The websites I used were:

I hope this post helps other people who are looking for Database jobs in the UK.

Edit: For thos interested, I have found a copy of a test some employer asks its future MS SQL developers to do.

Test

1. If table X contains 5 rows and table Y contains 7 rows, how many records will be returned by a CROSS JOIN on both tables?

a. 5

b. 7

c. 12

d. 35

2. What is the maximum number of clustered indexes that can be defined on a table?

a. 8,032

b. 1

c. Unlimited

d. Depends on the SQL Server edition

3. Which of the following statements is NOT true?

a. A composite index consists of more than one column

b. A composite index cannot contain an identity column

c. A composite index can also be clustered

d. A composite index can also be unique

4. Using the Latin1_General_CI_AS collation, what is the result of the following expression if @Country = ‘USA’?

ISNULL(NULLIF(@Country, 'usa'), 'United States of America')

a. USA

b. usa

c. United States of America

d. NULL

5. Which of the following is not a T-SQL function?

a. COALESCE

b. CONVERT

c. TRIM

d. NULLIF

6. When creating a VIEW, you can only specify an ORDER BY clause if:-

a. TOP is also specified

b. the SCHEMABOUND option is also specified

c. the GROUP BY clause is also specified

d. all the columns specified in the ORDER BY clause are contained in the SELECT list

7. What does the following query do?

SELECT COUNT(so.name)

FROM syscolumns sc

INNER JOIN sysobjects so ON sc.id = so.id

WHERE sc.name = 'Site' AND xtype = 'U'

a. It counts the number of COLUMNS in the VIEW named ‘Site’

b. It counts the number of VIEWS which contain a COLUMN named ‘Site’

c. It counts the number of TABLES named ‘Site’

d. It counts the number of TABLES which contain a COLUMN named ‘Site’

8. How do you produce a carriage return and line feed in SQL Server?

a. CHAR(10) & CHAR(13)

b. CHR(10) & CHR(13)

c. CHAR(13) + CHAR(10)

d. CHR(13) + CHR(10)

9. Which of the following statements would you use to disable a trigger on the Employee table called Employee_ITrig?

a. DISABLE TRIGGER Employee.Employee_ITrig

b. ALTER TABLE Employee DISABLE TRIGGER Employee_ITrig

c. ALTER TRIGGER Employee_ITrig DISABLE

d. ALTER TABLE Employee DELETE TRIGGER Employee_ITrig

10. What is syntactically wrong with the following statement?

UPDATE Person SET Surname = 'O'Reilly' WHERE PersonId = 10

a. If PersonId is not unique, you may update more than one row

b. There statement is missing a fully qualified table name

c. There is an unterminated literal string within the statement

d. There is an ambiguous field name within the statement

Decisions... Decisions

2008-09-03T16:00:00.000+08:00

In my last blog post, I discussed storing conditions in the database. For a quick reminder, I suggested that you store all the under-lying conditions (the if-then-else statements) from your code in your database. From my example, I used "did the sales person sell more then average for that day" ( sold_more_than_average BOOLEAN) and the answer would be True or False.

In this post, I would like to discuss connecting those same conditions with a decision table.

Before I get started, I would like to take a moment and reminisce about some of the previous posts I talked about decision tables to help explain them better.

Decision Tables and Your Database - November 25, 2007
Where I talk about decision trees and decision tables.

Using Business Rules in MySQL - February 20, 2008
I have a very graphical example of how to use decision trees and tables in that post.

Decisions in Your Tables - June 30, 2008
My most up to date and well thought out explanation for understanding and implementing decision tables.

Storing Conditions - August 25, 2008

This was the prelude to this post. It explains the reasoning behind simply storing your conditions, while this chapter explains how to make further use of them along side decision tables.

So What is the Trick?

The trick is basically, you take the conditions you have stored and do a search in the decision table to get only one result back.

Confusing? read on. It will become clearer.

Example

Now I would like to show how to integrate conditions stored in the database with decision tables. Once again, I will do so with less words and more examples.

Story - Maintenance and Cleanliness in a Hotel

An owner of a small guesthouse would like to manage better the cleaning woman and how often she should clean the rooms of the guesthouse. After discussing the issue with her, they decide that she will clean each room, once every 2 days under the condition that there are guests using the room (don't look at me, I was in a guesthouse that the cleaning lady cleaned once every 3 days or whenever she felt like it).

The owner would also like the cleaning lady to clean rooms before new guests come in and straight after they leave so that people can view a clean room when no one is using it. Also, if there is something wrong with the room (like a broken sink) then there is no point to clean the room until it is fixed.

The owner would also like to keep track of booked and available rooms in this system.
Using MySQL WorkBench I designed the database model and it looks like this

Bare with me for the explanation. Lets take a look at the rather multi-columned Rooms table:

As we can see, at the top is the standard data that you would need for the table - room id and the day. I also needed some more input from the user, so I added those 2 extra fields.
The conditions are what I talked about taking the if-then-else statements and putting the results in your database.
Is_room_booked - is the room booked or will have people using the room on that day.
Is_first_day and Is_last_day - are conditions to know if the room needs a special kind of cleaning. First day needs to be prepared for visit and cleaned. On the last day, the room needs to be cleaned for viewing only (which could mean, no towels and toilet paper are left in the room).
has_been_cleaned_yesterday - has to do with the arrangement for the cleaner that she wont have to clean the room everyday. If she did clean yesterday, she doesn't have to today. If she didn't clean yesterday, then she does today.

The decisions are what you want to do with the data you have. Once you have the conditions, you need to query the decision tables. The decision table holds the combinations of the conditions and decisions. The query will then give you a result of action you should take.
When you have all the combinations pre-defined, you have no side effects. That is to say, if you have if-then statement (that you wrote it quickly), you might have missed a side effect when you didn't use an ELSEIF statement for a certain condition.

(Please pretend that 0 is False and 1 is True)
Here we have the decision table with all (or most) of the conditions that might happen and their results. Notice the primary keys at the top of the table to ensure that there wont be 2 conditions twice in the table (another benefit to use relational database with decision tables). Please also notice that I could have just joined the conditions to this table, but I decided to keep a copy of the decision in the main table so that if any changes are made, the old results will still remain in the main table.

So What was that Trick Again?

As I mentioned before, you take the conditions from the table, you use those conditions to do a search in the decision table. So you can do:

SELECT * FROM decision_maintenance WHERE is_booked = True AND....

You can then insert the results into the rooms table, since you should only get one result. You can even set up a Trigger in the database to do it for you.

Small Note - If you forgot to input a line in the decision table, you can make a function (in any language) that if you do not get a result from the decision table that it should add a new line and send an error to the user to choose a decision for those conditions.

And Now, The Code

If you don't need to see this part, skip ahead.

(Code from MySQL WorkBench - p.s. I had an error on a constraint with this program. I had to remove it manually)

-- -----------------------------------------------------
-- Table `mydb`.`customers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`customers` ;

CREATE TABLE IF NOT EXISTS `mydb`.`customers` (
`idcustomers` INT NOT NULL AUTO_INCREMENT ,
`customer_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`idcustomers`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`rooms_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`rooms_info` ;

CREATE TABLE IF NOT EXISTS `mydb`.`rooms_info` (
`idrooms` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`room_name` VARCHAR(45) NULL ,
PRIMARY KEY (`idrooms`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`rooms`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`rooms` ;

CREATE TABLE IF NOT EXISTS `mydb`.`rooms` (
`idrooms` TINYINT UNSIGNED NOT NULL ,
`date` DATE NOT NULL ,
`idcustomers` INT UNSIGNED,
`has_been_cleaned` BOOLEAN NOT NULL ,
`has_some_problem` BOOLEAN NOT NULL ,
`is_booked` BOOLEAN NOT NULL ,
`is_first_day` BOOLEAN NOT NULL ,
`is_last_day` BOOLEAN NOT NULL ,
`has_been_cleaned_yesterday` BOOLEAN NOT NULL ,
`need_cleaning` BOOLEAN NULL ,
`prepare_for_visit` BOOLEAN NULL ,
`available` BOOLEAN NULL ,
PRIMARY KEY (`idrooms`, `date`) ,
INDEX fk_customers (`idcustomers` ASC) ,
INDEX fk_rooms (`idrooms` ASC) ,
CONSTRAINT `fk_rooms`
FOREIGN KEY (`idrooms` )
REFERENCES `mydb`.`rooms_info` (`idrooms` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`decision_available`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`decision_available` ;

CREATE TABLE IF NOT EXISTS `mydb`.`decision_available` (
`is_booked` BOOLEAN NOT NULL ,
`has_some_problem` BOOLEAN NOT NULL ,
`available` BOOLEAN NULL ,
PRIMARY KEY (`is_booked`, `has_some_problem`) )
ENGINE = MyISAM;

-- -----------------------------------------------------
-- Table `mydb`.`decision_maintenance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`decision_maintenance` ;

CREATE TABLE IF NOT EXISTS `mydb`.`decision_maintenance` (
`has_been_cleaned_yesterday` BOOLEAN NOT NULL ,
`has_some_problem` BOOLEAN NOT NULL ,
`is_booked` BOOLEAN NOT NULL ,
`is_first_day` BOOLEAN NOT NULL ,
`is_last_day` BOOLEAN NOT NULL ,
`needs_cleaning` BOOLEAN NULL ,
`prepare_for_visit` BOOLEAN NULL ,
`prepare_for_view` BOOLEAN NULL ,
PRIMARY KEY (`has_been_cleaned_yesterday`, `is_booked`, `is_first_day`, `is_last_day`, `has_some_problem`) )
ENGINE = MyISAM;

INSERT INTO `mydb`.`decision_maintenance` VALUES (False,False,False,False,False,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,False,False,False,True,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,False,False,True,False,True,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,False,False,False,False,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,True,False,True,False,True,True,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,True,False,True,False,True,True,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,False,True,True,False,True,False,True);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,False,True,True,False,True,False,True);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,False,False,False,True,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,True,False,True,True,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (True,False,True,True,True,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,False,False,True,True,False,False,False);
INSERT INTO `mydb`.`decision_maintenance` VALUES (False,True,False,True,True,False,False,False);

INSERT INTO `mydb`.`decision_available` VALUES (True,False,False);
INSERT INTO `mydb`.`decision_available` VALUES (False,True,False);
INSERT INTO `mydb`.`decision_available` VALUES (False,False,True);

Conclusion - Some Pro's and Con's

1) Saves CPU usage by using more hard disk space
Pro: Saves hard disk usage and CPU usage from repeated processing.
Con: Uses more space on the hard disk and uses the CPU (probably when you insert data) to processes it one time

2) Helps with data analysis

Pro: Simplifies OLAP and BI operations

3) Leaves clues about how the data was processed.

Pro: Helps a lot with debugging and possibly documentation.

Con: Keeps extra data that might not be needed for later.

4) Helps with the design of the database as well as with the code.

Pro: While planning the database design for the example, I ran into many situations where I re-did some of the design during the actual design process. This was because I had to think ahead for which conditions I might need for the decisions. I found this far more helpful then designing the database first, then finding out during the coding phase, that I need to make changes to the database.

5) Helps manage decisions

Pro: Allows you to "configure" your decisions by making simple changes to the table. As apposed to looking through your code to make the changes.

6) Leverages regular database search functionality to use your decisions

Pro: You can use regular SELECT statements to find out which decision to take.

Con: Even though you can quickly find which decision to take, you cannot take the action needed. The database will just give you the data and you might have to set up CASE or IF statements in your code to know what to do with the decisions.

Some more points which are arguable..

7) Is database and programing language agnostic.

You are not locked in to database vendors or "slow" programming languages, because you can relatively easily change either database or programing language. The database just keeps the data, the programming language is restricted to very defined data it needs to provide to it and the actual decisions/rules are mostly kept in the database but as data.

8) Scales

Using single table is faster then using JOINs. If you have a table with everything you need inside of it, then in theory, you could scale better. As well as the fact that you might not need to process data again and again. I understood that facebook uses very very few joins in their code and that everything is in single tables (they might duplicate the data to keep it that way).

9) Keeps the data cleaner

Only if you setup conditions for that to happen, but could still help analyze the data for integrity.

10) Develop software faster

In connection to point 4, but a shameless statement that would need much more proof to back it up.

Bibliography

Decision tables have been studied in academia since the 80s. Here are some papers on the subject touching on different fields, from data mining to artificial intelligence:

Decision tables: Scalable classification exploring RDBMS capabilities

The power of decision tables

Relational Data Mining

Using Neural Network Rule Extraction and Decision Tables for Credit–Risk Evaluation

If you have any feedback about my idea, I would be glad to hear it.