When you start a business, predicting how your database needs will change is hard. As the business grows, you might find yourself adding to your database in ways that make the architecture more complicated and make serving your customers increasingly difficult. That's what happened to one of our readers, King, who runs a Web-site hosting business. King provides his customers with several kinds of Web-hosting services:
- registering and hosting a domain
- hosting a domain (preregistered)
- transferring and hosting a domain from another site
- parking a domain (no hosting services needed)
Each type of Web-hosting service comes with a different fee structure. For example, the fee to park a domain is less than the fee to register and host a domain. The owner of a hosted domain can choose from a list of additional options that King offers, including secure certificates, email user accounts, listserver services, additional disk or database space, and streaming media functionality.
King is experiencing difficulties with the database that he created to manage his user accounts—specifically, the invoicing portion of the database. Most of the time, King can charge the fees to a customer's credit card, but occasionally a customer wants to pay by check. King needs to invoice these customers periodically. And eventually, King wants to be able to accept electronic payments.
Some of King's design problems result from the variety of services and options that he offers to his customers. Figure 1 is a simplified version of King's original database architecture, which made invoicing a challenge. As you can see, a separate table represented each option. Customer charges were scattered among five tables (tables with attributes highlighted in yellow). Costs for these options and services were stored in seven other tables (tables with attributes highlighted in gray). This scattered arrangement made it difficult for King to do simple things such as generate a statement of accounts for a customer who subscribed to several Web-hosting services. King also started running into difficulties when he tried to generate accurate reports. He wanted to query the database at any time and be able to see which pay-by-check customers were behind in invoice payments, pull a list of credit-card charges for a particular pay period, or post a payment for any outstanding invoice.
Figure 2
shows my solution to King's dilemma. When I view the database, I see that King is a purveyor of goods and services. As such, King's database can use the standard template for invoicing goods and services—with a twist. King's customers aren't one-time buyers—they're subscribers. Most invoicing metamodels assume a one-time purchase of goods or services by a customer, with payment rendered in cash, by check, or by credit card at the time of purchase or shortly afterward. In King's business, the customer (CUSTOMER) is a subscriber who might have registered many domains (DOMAIN) or no domain. A customer with no domain is called a prospect. Each registered domain needs at least one service (SERVICE PLAN) and is charged at least the periodic hosting or parking fee. The customer might want to set up different payment plans (PAYPLAN) to pay for the services that each domain uses. Each payment plan can be associated with its own billing address (ADDRESS), credit-card information (CC INFO), method of payment (PPMethod—e.g., cash, check, credit card, electronic funds transfer), billing cycle (PPCycle—e.g., monthly, quarterly, semi-annually, annually), billing day of the week (PPBillingDay—e.g., MON, TUE, WED, THU, FRI, SAT, SUN), and payment terms (PPTerms—e.g., net 10, net 20).The various options and services that King's business offers are contained in the generic HOST SERVICE LIST table, which King might need to modify slightly to ensure that it can accommodate all the different types of services that he wants to provide—even those services that he hasn't listed in his current database. Each time a domain uses one of the available services, the use is recorded in the SERVICE USED table, which the billing system uses to create the periodic invoice that King sends his customers. King can override whether a service is billable (by changing the SERVICE USED table's SUBillable column), and he can choose which customers' invoices to print by changing the value of InvGenerateBill in the INVOICE table from N (no) to Y (yes).
King can now create views that show the statement of an account by domain or by customer. He has a much simpler set of tables to deal with, and he can easily keep track of which services are selling best. He can anticipate which services his customers will want by studying subscription patterns, and he can do some advance planning and invest in infrastructure to serve his customers better. He now has a more flexible design on which to build his business.
The database design that Figure 2 shows is a moderately denormalized physical data model. In my experience, this level of denormalization is typical when you need to redesign an existing database. For example, in the INVOICE table, notice that six different dates are involved with each invoice. Four of these six dates are actually states of an invoice—approved, sent, voided, and paid. In a fully normalized design, you'd have a separate table called INVOICE STATE (a child table to INVOICE) with three columns—INVID, Invoice State, and Invoice State Date—which you'd use to capture the states and the state dates of the invoices. I created this moderately denormalized table architecture for two reasons. First, I determined that I could do a limited level of denormalizing while still maintaining the integrity of the data. And second, I didn't want to disrupt King's current database any more than necessary.
When you're buried in the details of running a business, as King has been, it's hard to see which direction you need to move in to create a database design that will do what you need it to do. By backing out of the details and looking at the requirements from a higher level, I was able to craft a simpler database solution that lets King expand the products and services that he's currently offering while making it easier for him to generate the kind of reports he needs to run the business successfully.
