I never really thought to ask the question, but was shocked to see you can do simple conditional statements in Mysql. This will make my querying far better than in the past. Plus I can pull quick data when needed instead of writing a script to analyze it.
Example, trying to find out if an order was placed in our callcenter or on the web:
SELECT orderDate, if(ipAddress = ‘XXX.XXX.XXX.XXX’, ‘CallCenter’, ‘Web’) as WherePlaced, count(*) as cnt FROM orders GROUP BY orderDate, WherePlaced
Simple, but only useful if you know about it. Now I do. 🙂
I have been thinking about a way to have isolated machines that I can almost take on and off line very easily. Here are my thoughts and would love feedback on my “solution”.
I am almost thinking about “spinning” the traditional model. I will have my Administrative DB(ADB) in a central place. ADB will be where I pull all reporting, master updates of product and pricing, all orders, etc….
On each individual web server I will have a slave DB (SDB) that will replicate all data from ADB. The individual web servers will pull information from SDB for pricing, dynamic web pages, etc… It will also have its own database (ODB) for writes (order information mainly, maybe other statistics as well). When an order is placed (instead of trying to reach ADB) it will put the information into ODB.
Now I can have a cron job that will pull the data off each ODB to update ADB for reporting purposes.
Each web server should be able to run without relying on any other machine.
Once the web server’s SDB is up to date, there should be very little delays in price/product changes.
Scalability, simply launch a new server to be up and running.
Does this make sense?
Web servers write to ODB
Administrative server reads from ODB to update itself which replicates to all the SDB (Every slave/web server becomes a realtime backup of ADB)