Quintified

One of our clients’ database systems feeds content into third-party websites. Our client has recently signed on a very high traffic website, and a five-minute test yesterday revealed that we have some optimizing to do be able to handle that website’s traffic.

I made some code changes, added an index to a database table which should up in the slow query log during the test, and was surprised to realize MySQL query caching wasn’t turned on for that server, so it is now. And for another test this afternoon I’ve put the code on a faster server.

But the most exciting change I made was implementing and incorporating memcached, a “Free & open source, high-performance, distributed memory object caching system.” I had known about it for years — Fabrizio Parrella at The Queensboro Shirt Company set it up as one of the first things he did when he got there — but I hadn’t ever messed with it, and our servers’ loads were such that it wasn’t a real need. (Business database applications tend to have much fewer page views than high-profile public websites.)

But last night I had a real need, and found memcached to be an amazing tool.

Here’s how I explained it in an email to my programming teammates this morning:

The basic idea with memcached is that you can store data (text, arrays, etc.) in RAM for quick retrieval instead of having to pull it out of the db each time (which involves hard drive reads among other things), and this cache is session/user/page view independent.

In a nutshell here is the magic:

In the past I just had:

———————–

$offer_info = maj_get_offer_info($offer_id);  // how we get info out of the db into our script

———————–

Now I have:

———————–

$seconds_to_live_in_cache = 120;
$key = "offer_info_{$offer_id}";
$offer_info = memcache_get($memcache_instance, $key);
if (!is_array($offer_info))  {
   $offer_info = maj_get_offer_info($offer_id);
   memcache_add($memcache_instance, $key, $offer_info, false,  $seconds_to_live_in_cache);
}

———————–

The key to appreciating this is that if John Doe loads up a page that uses the database to get $offer_info, with that then getting put into memcache, and then 90 seconds later Susan Smith accesses the same page with the same offer, Susan’s page is going to pull the offer_info from super-fast RAM instead of making a database call (or actually a set of several database calls).

And for higher traffic sites, the memcached server can be a different server than the web or db servers, or you can have multiple memcached servers.

My example puts an array into the cache, but you could also use it for example to put a block of html into it, such as the left nav html content on Port City Deals (featured deals, search by category/location, price ranges). All of that is info that is very rarely changed, so the whole block can be built and then cached for a long period of time, saving MANY database calls with each and every page view.

In posting this online I’m sure some are going to say “so where have you been for the past several years?”, but I’m excited about my newly discovered tool. My next step is to build it into our code generator from the ground level up.

Until now we haven’t ever spent much effort or attention on “the pretty” — the visual aesthetics of our database systems. We’ve intended all along to incorporate a nice design at some point, but until now, ours and our clients’ focus has been on adding great functionality. In the meanwhile, we’ve been told again and again by friends, families, prospects, and ourselves that design is fundamental and “pretty” is going to sell better.

Well, tomorrow we have a big opportunity to demo one of our products to a franchise with many franchisees, and knowing that we have exactly what they need in terms of functionality, we just didn’t want to turn them away from a less-than-positive first visual impression.

So Daniel has been burning the midnight oil and has made some awesome progress on this that we’ll be rolling out into our products soon as well as demoing tomorrow. (Yay Daniel!) I’m quite thrilled at the progress but in some ways it’s like looking in the mirror and not recognizing yourself.

We spent the majority of today working on incorporating the new design into our code generator, and it’s going to be a long night. And come morning we probably won’t yet be at the “luscious eye candy” level. But we’re going to be looking much better, perhaps even respectable!

In addition to some behind-the-scenes programming development and improvement of our internal systems, here are some exciting new features that we at Quintify have rolled out recently into all of our core products:

Global search now looks inside of uploaded PDF files and Microsoft Office documents and includes their contents in search results.

I’m not sure if I’ve mentioned global search in this blog, but this cool feature allows you to search across all of the “things” in your system in one search box and one set of search results ordered by relevancy. This way you can search client records, notes, orders, products, jots, projects, tasks, and so on from a single search box. (Compared to our “filters” search capability that allows you to drill down with precision, this global search capability allows you to cast a wide net. Both complement each other very well.)

Now, these search results include the contents of the PDF files and MS Office documents you’ve uploaded into your system. I played around with this by creating a one-sentence MS Word document that said “how now brown cow”. Then I uploaded that into our database system and did a global search for “brown cow” and my Word document showed up in the search results. Nice!

Improved lead/client import via a spreadsheet upload

In the past our system was “picky” about the spreadsheets that could be uploaded into them to import data — in particular the columns in the spreadsheet had to be in a certain order, and if you weren’t including information for one of the fields in the database you had to include that column but leave it blank. And you could only include information for a particular set of fields/columns. (Sorry about that!)

Now, the lead/client upload page does not require your spreadsheet’s columns to be in any particular order, and you can also add addition columns  of information that can be added in as client notes.

“Quick jump” links on the “List Clients” and “List Documents” pages that allow you to quickly pull up the status or category that you want.

A client with many different kinds of many documents in their system asked for a quick way to filter to a particular category of documents, e.g. “forms” or “blank contracts”. We had wanted similar functionality for our own system and went ahead and included it in all of our core products as well.

This is particularly helpful on the “List Clients” page, because with it you can immediately jump to your list of “Suspects”, “Prospects”, “Shoppers”, “Customers”, etc. — or whatever set of client statuses you have set up in your system.

Enjoy!

As a computer programmer, I’m a big fan of Stack Overflow — both the website and the podcast, and I was thrilled when Stack Exchange came out so that anyone can have their own Stack-Overflow-like website on their topic of choice. Today I took the time to set up Quintify’s Stack Exchange website.

Quintify::Answers — found at http://answers.quintify.com — provides a forum for Quintify customers and prospects to ask and answer questions about using their Quintify databases. We anticipate a Quintify user community growing around this over time, and Quintify team members will monitor it to provide regular how-to info for those asking questions.

Right now there is only one question on the site — asked and answered by me — but eventually the site will be a large knowledge base easily accessible by all Quintify database users.

We have some exciting news to share with you!  Quintify recently launched several industry-specific database solutions designed to provide small business owners the tools they need to run and grow their business.  The web-based databases, which are geared towards business coaches, virtual assistants, web developers and small and medium sized businesses, include the following features:

• Customer relationship management (CRM)
• Project / task management
• Quotes, orders and invoicing
• Services and products
• Email marketing
• Time management and time clocks
• Client portal
• Robust reporting
• And much more!

Each database product is offered for a low monthly fee of $55 per month.  There is no contract to sign, no long-term commitment, and no hidden fees.  Additionally, there are no limits to the number of users who can access the system or the number of records that can be stored. 

To learn more about the products or to sign up for a FREE 30-day trial, please visit the websites below.

Quintify::Complete - an ideal solution for small and medium sized businesses
 
Quintify::Business Coach - perfect for business coaches who need to keep in touch quickly and easily with their clients
 
Quintify::Virtual Assistant - great for virtual assistants who need to keep track of their tasks, perform invoicing or other administrative tasks
 
Quintify::Web Developer - amazing tool for those who design and develop great websites

If you know someone who might benefit from one of our products, please feel free to link to this blog entry!

PS:  Know someone who is looking to start up a business for a very low cost?  Visit http://www.mytowndeals.biz for more information on an excellent home-based business opportunity for budding entrepreneurs!

On Thursday (11/12) UNCW will be hosting the Wilmington Area Professionals IT Breakfast in the Madeleine Suite from 7:30 AM - 9:15 AM. The topic will be the “Impact/Future of Cloud Computing.”

I’m going to be one of the speakers, and will spend the majority of my time discussing things we and others are doing with Amazon’s Web Services for cloud computing. This is a fascinating topic for me and I’m particularly excited about Amazon’s Relational Database Service (RDS).

I’ve stayed up late tonight reading “Traction: Get a Grip on Your Business” by Gino Wockman. It’s awesome, and I’m very excited about implementing it in Quintify.

For a while now I’ve wanted us to build in a “business development” module into our Quintify business database software to go along with the CRM, project/task management, order fulfillment, invoicing/AR, marketing, CMS, etc. that we already include. I’m going to build Traction’s model into our own database system, and if it goes as well as I think it will, I’m going to talk to Gino’s company about us having a Traction-branded version of Quintify::Complete. (One nice thing about our code generator is that such stuff can get implemented pretty quickly. I’d also like to create an ActionCOACH-branded version using their business development model.)

I’ve heard the things in Traction before — from Michael Gerber, Reggie Shropshire, and others. But Traction really explains things in an easy-to-understand-and-implement way. Or perhaps I’ve been exposed to the concepts enough that they are finally taking root.

(I’m speaking at an undergraduate database class today at UNCW and needed a place to stick some supplemental notes, and thought I’d put them here.)

Some best practices in data modeling for web application development, particularly for “backend” business systems:

1. Initially focus on database tables, fields and relationships and not what the web pages are going to look like. If the data model is right, the user interface will (pretty much) take care of itself.

2. For all major “things” in your system, have a table for notes: product_note, client_note, quote_note, task_note, etc. (And encourage users to add notes.)

3. For each database table, have the following fields:

• date added
• date last modified
• last modified by

4. At the application level, have edit locks to prevent two users from trying to edit the same record at the same time, with the one submitting the form last overwriting the one who submitted it first. (At the data model level, in addition to the three recommend fields above, also have “date last locked” and “last locked by”.)

5. Think through (and discuss with your client) one-to-many vs. many-to-many relationships. For example, when setting up a database for a school, does each class have one and only one teacher or can a class have multiple teachers? When discussing this with a client, ask them if the relationship in question needs a select list (one-to-many) or checkboxes (many-to-many) in the web form. In our example, some schools will be one-to-many but others will be many-to-many. Getting this right from the start will save headaches down the road.

6. Log user access — both system access (logins) and page access (page views and actions). You probably won’t often need to know who accessed what page when and from where, but when you do, having that info in the database is golden.

7. Be careful about losing historical information. For example, if you have a product table that contains “unit_cost”, you might be tempted to not include “unit_cost” in your order_line database table, since that info is already available in the product table and “don’t duplicate info” is a value. However, over time the products’ costs are likely to change, and at that point you won’t know what the cost was for a product ordered last year unless you captured that info in the order_line database table. The solution is to have the “unit_cost” in the order_line table.

8. When setting up the data model for user access, seriously consider having users and usergroups be “many to many”, so that user access to particular parts of the system is “nonlinear”. I’ve seen others’ systems where each user could be given basic access, or intermediate access, or admin access, and I’ve heard about a system where one’s access rights were a scale from 1 to 100, with someone at a 70 getting everything below that, and someone at an 80 getting everything below that. It’s much better to have multiple usergroups (e.g. customer service, accounting, marketing, etc.) and then allow users to be in multiple usergroups based on their roles within the organization, which each usergroup having its own set of access permissions.

9. I name all of my “join table” tables with a “j” at the front, e.g. jstudent_class and jemployee_usergroup. This is really helpful for quickly knowing what relationship type the table encompasses.

10. Sometimes calculated fields are OK. For some reason at one point in my life I was under the impression that calculated fields in database tables were evil, but there are times when they are very helpful, particularly depending upon the framework you are using. With wm, we can set a flag for a field being a calculation, so that it doesn’t show up in the add/edit form, but it does show up in the display and the search filters. The only catch is that we need to add the code for the calculation, but we have things in place to handle that.

———–

Any comments, or any more you’d like to add?

Our feature of the week is something I know everyone will find useful.  Say you’d like to quickly create a segment - adding all clients located in the state of California.  You’ll be using this segment to send out a targeted email blast to those clients, perhaps with a special offer.  The feature of the week will show you how easy it is to do this - takes less than two minutes! 

Information release from Kim LeClair of CFCC’s Small Business Center (Wilmington, NC):

————————–

Database Development for Your Small Business

September 10th from 6-8 p.m. / Room L-107

FREE SEMINAR

I am pleased to announce this NEW seminar that will help many businesses run more efficiently. Below is a course outline of what will be covered. Mr. Reid Wilson, from Quintify Database Solutions, will be presenting this seminar.

Course Outline: Are you currently storing all your clients in an Excel spreadsheet? Are you sticking with a legacy system that no longer meets your needs? Many small businesses are using outdated means to store their contacts, do process management, and provide actionable reporting. As part of this seminar, we will show you the 8 key benefits of having a great database:

• Reduce complexity
• All key info is in one place only
• Productivity gains
• You can scale
• Knowledge to make decisions
• Easy customer contact
• Gives the business owner peace of mind
• Immediate ROI plus builds long-term financial value

I am attaching a registration form for those that are interested in attending. If you have any questions, you can reach me directly at 362-7216. Have a wonderful day.

Kim LeClair

SBC, Cape Fear Community College