One of the questions we often get in our intro to databases (conceptual intro) course is…in what kinds of real world problems could we use a database? One answer is consider using a database to help track a lot of data or complicated data, or both.
We wrote a recent blog on stopping junk mail so we won’t duplicate that, but instead expand on it to explain how you might use a database to track contacting companies to opt out of mailings and other activities.
Although in our intro to databases class we go through several case studies explaining different types of applications for which students could use databases, this blog posting discusses an applicable example. As with blog postings, we won’t go into any implementation details (see our Programming Tips for coding!), but rather just pose certain questions. This blog is by no means assumed to be the best way or only way to stop junk mail; it’s just one idea.
The basic idea is to model the opt out workflow that captures the data and automates as much of the letter writing, label printing, reporting, and other activities.
Getting started…
So how exactly would you stop all junk mail? What would the database need to look like (table structure)? What types of responses from companies would you need to track? How long does it take to stop all junk mail?
All good questions.
Considering the OPT OUT portion of the database:
To set up a database, first consider that you need to capture the company information but also each response you get from the company. Should you have a separate address table? (Probably) In database terminology when you have a single table possibly related to multiple items in another table, this is a “one to (possibly) zero or many” relationship. In our intro to database course we talk about different types of relationships that you would model in most databases. We also discuss the cardinalities with these relationships (1:M, 1:1, M:M, M:1).
Document Your Assumptions for the System Up Front:
One initial “getting started” activity is to write down all your assumptions and then examine them later for refinement. It’s nearly impossible to get it all right the first time since what happens in the real world will sometimes defy logic. Your assumptions could be perfectly reasonable, yet the real world may challenge them.
One assumption you might reasonably make would be that you could just call the company one time and they’ll stop sending you junk mail, but this is rarely the case. Sadly, often, it will take two, three, four, or more attempts to get the company to stop sending you junk. Your contact attempt may include letters, phone calls, and in the worst case even submit a USPS prohibitory order so the USPS will contact that company on your behalf. Do you see this might be a “one to many” relationship somewhere in our database?
Determine if Your Application Is a “Workflow”.
Since all of these activities involve workflow (do step 1, then step 2, …) you want to have the the database’s data entry, and other activities, fit that model.
With a prohibitory order, for example, you can automate much of the data entry using the data you already entered on the OPT OUT form. You already have, for example, the company information from the OPT OUT form so you could copy it (or just “link it”, referentially, depending on your design). Also, since at least two prohibitory order dates depend on the date the order goes into effect, then the database should calculate these two dates automatically after you enter the initial date.
Create a workflow diagram
The diagram below is a (early, draft) starting point to model what we want using a workflow approach. Once you diagram the system, it’s easier to spot workflow problems or other things in the database that might still need attention. You’ll more easily see that the diagram may be missing some decisions or flows. Or, possibly the flows that are there need refinement. Don’t focus on trying to get everything perfect in version 1, but rather get the basic flows as close as possible to how you envision they would actually work.
Note that the diagram below uses a UML “State Diagram”, which is useful for modeling workflow models.
Automate Letter Writing
Your database could also have multiple form letters you pick when you write a company to “OPT OUT” of receiving junk mail from them. You could modify the form letter for each company, if needed. You could automate printing a label for the envelope, too. The database could then save each letter you sent into the multiple contacts (as many as needed) for that company. You could even track postage.
Reports
You’ll also probably want a report showing you which orders are still in effect on the date you run that report. Another key use for a database: reporting.
Graphs:
Maybe you want a graph showing the worst junk offenders or possibly junk mail by type of mailing. How about a report showing the list of companies and the number of contacts for each company ordered by date? Your database may offer graphics or you may need a plug-in for your existing database.
Scanned Images
You’ll have to decide how much to scan and keep as part of the database. In this actual working example database, we captured the prohibitory order application, and the USPS prohibitory order received as searchable PDFs. Back in the OPT OUT form, we had the ability to capture an image in any of the possibly many interactions with a particular company (remember that the company table is related to one or possibly many contact actions. You could conceivably have zero actions if you chose to enter a company in by itself with no junk mail from them.)
Surprises along the way.
One of the things you will probably notice, once you start calling and writing companies to stop sending you junk mail, is that many companies are not really in charge of their data. Either they “farm out junk mail sending” to some third party or they don’t have sophisticated database folks you can talk to on their toll free number(s). If the company farms out their junk mail sending to a third party, you may never be able to get the message to the right person. It’s also possible since these companies like to send junk mail, that their hearts are not in stopping it just because you called or wrote them.
USPS Prohibitory Order – When they just refuse to stop sending junk
Fortunately, the supreme court decided in the ‘70s that you can stop any mail you do not wish to receive. Simply fill out PS-1500 and send it to the USPS (note the instructions). As described above, the workflow would capture all that information as well.
Conclusion:
We’ve covered a lot. Specifically, in our database, we are:
- Enforcing a workflow
- Automating date calculations
- Reporting so we know how we’re doing
- Graphing for getting the big picture quickly
- Supplying canned form letters
- Automating other activities including data validation
- Fixing user entry errors: if they enter “ca” for California, automatically change this to “CA”. Use Title Case fixes in other case, all automated!
- Tracking stamp costs in OPT OUT and Prohibitory Order forms
- Doing basic accounting for stamp payments
- Tracking the average time between junk mail for both new junk mail and junk mail actions with existing companies. This display uses a nice bar graph on our OPT OUT form.
- Tracking the average time it takes USPS to process a prohibitory order after we send it (about a month!)
- Printing labels if desired
- (More!)
Here is the nice graph that we show for 10 above:
————-
It’s probably clear that trying to do all these activities with complicated data, without a database, would be difficult if not impossible.
Even with automation, expect stopping junk mail to be, probably, a six month effort. (Companies don’t always make this easy.)
The database makes tracking and reporting straightforward, but for even low to moderate junk mail (a few pieces a day), stopping junk mail will take serious dedicated effort.
Finally, getting a “perfect” database application to model the real world is quite difficult. Consider all the bug fixes and updates for every piece of software you use. No other company, anywhere, can get any of their software defect free either. Perfect software does not exist so expect your database application to be a work in progress as you make it better and better over time.
Enjoy!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Please read our disclaimer available from our home page