Someone called the office last week to ask about a custom web application for showing a list of vendors to visitors based on the visitor’s selection of a state and city. She had an Excel spreadsheet with vendor information, and she wanted that turned into a MySQL database that she could edit and update online as well.

CRUD

That kind of web application is a common web coding proposition. So common, that it has it’s own name: CRUD, which stands for Create, Read, Update, Delete. The particulars are always a little different, which is why it often calls for a custom solution, but the basic idea is pretty standard. You need to have a page (ideally password protected), where an administrator can Create new records in a database (add a new vendor), Update (or edit) the records (vendors) already in the database, and Delete those that need to be removed. You also need to be able to Read the database to see what’s there. What you see when you read the information depends on whether you’re an administrator or an ordinary visitor to the site.

Unfortunately, the person making the inquiry didn’t have the budget to do the project. But it got me thinking about how I would do it if I were going to do it.

Start with the Data

Just about any web application that deals with data will need to store it in some kind of database. There are plenty of options to choose from, when it comes to database software for the web. No matter which you choose, the critical thing about databases is to get their structure right. The fancy word for it in database world is “normalization,” and essentially it comes down to making sure you don’t repeat the same information in different places. Ever.

Take this sample spreadsheet someone brought to us (slightly altered here to shield that someone from too much embarrassment), for example:

Bad Data Structure ExampleThe intention of this layout is to designate certain vendors in “Business Group A,” others in “Business Group B,” etc. Perhaps vendors in one group get certain discounts, or have access to different product lines, or have different credit ratings. Whatever.

The problem, though, is in the repetition of columns with the same kind of vendor information: Business Name, Business Website. This leads to all kinds of trouble. For starters, to find any given business, you have to know ahead of time which group it’s in, to find which set of columns to look in. If you don’t know which group to look in, you need to cycle through the column sets for each group. All this makes designing a step-by-step way for your script to find what you’re looking for complex and error-prone.

The better solution is to recognize that, in addition to a name and website, each business also belongs to a group. Instead of having all those columns to take care of grouping, it’s much easier to have just one column: “Group” that tracks which group a business belongs to. As you can see, the resulting table is much simpler.

Revised Data Structure ExampleYou can now select a business based on it’s group by simply pulling all the rows, say, where Group is B, and you immediately have all the information (name, location, and website) about every one of those businesses at your disposal.

Conclusion – DRY coding Is Essential

Getting your data structure right is the first critical step in building any web application. The cardinal rule for getting data right is to never repeat yourself.

As it turns out, “never repeat yourself” is a kind of “coder’s creed.” It applies to just about everything. It’s so important that programmers even have a name for it: DRY coding (Don’t Repeat Yourself).

So, until next time, happy coding, and when the CRUD hits the fan, stay DRY.