Announcements

Conceptual Database Application Example: Getting Rid of Junk Mail

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.