In my last column, I described a common business problem: A SalesOrder with multiple OrderLines, with each OrderLine specifying a Product being bought by a Customer and the SalesOptions the customer has chosen to apply to that OrderLine/Product combination (e.g. giftwrapping, expediting). Those SalesOptions affect how that OrderLine/Product combination will be processed, including calculating the price for that purchase. In that column, I looked at a couple of patterns that might support a solution that was maintainable, extendable, testable and understandable. In the end, I decided that calculating the price should be handled by the decorator pattern because it would allow the SalesOptions to interact as required by the organization’s business rules (e.g. expediting increases the price of the Product by a set percentage after all other discounts are applied and ignoring any costs associated with giftwrapping). To deal with the rest of the processing required by the SalesOptions, I decided my best choice was to implement some version of the Roles pattern.
This column will look at the data design required to support implementing the solution.
I’m old fashioned enough to begin to develop any solution by designing the database tables that I’ll need in my relational database (of course, if this organization had enough sales orders they might need a big data solution—but that isn’t the case here). Obviously, I’ll need a table listing valid SalesOptions. The primary key for this SalesOptions table is the SalesOptionId and the table has at least one other column: the SalesOption description, which is displayed in the user interface when the user is selecting or reviewing the SalesOptions for an OrderLine. Any data about the SalesOption that doesn’t vary from one Product to another will also go in this table.
Because not all SalesOptions can be applied to all Products, I also need a table of valid SalesOptions and Product combinations (the company sells both “things” and services, for instance, and you can’t gift wrap a service). This ValidSalesOptionsForProducts table would have a primary key made up of two columns: the ProductId and SalesOptionId. The table might have some additional columns to hold data related to the relationship between a particular Product and SalesOption.
If there’s no data associated with a Product/SalesOption combination, however, there’s another data design possible. If most SalesOptions apply to most Products, it would be more efficient to create an InvalidSalesOptionsForProduct table that lists just the Products and SalesOptions that can’t be combined. If the organization has thousands of products, a table of exceptions would be more efficient than a table of allowed combinations.
And before I get tons of comments about using natural keys/real data as the primary keys of my table: You’re perfectly welcome to give the ValidSalesOptionsForProduct table a meaningless primary key (a GUID or Identity key of some kind) and apply a unique index to the combination of SalesOptionId and ProductId. SQL Server’s hashing mechanisms for building an index will almost certainly give your better performance whenever you need to use that meaningless key as the foreign key of some other table. But, for this discussion, I don’t need that meaningless primary key so I will ignore that option. That also applies to the next table that I’ll discuss.
For any particular OrderLine, I’ll also need a table to record the SalesOptions that have been applied to it. That SalesOptionForOrderLine table will have a primary key, or unique index, consisting of the OrderId, the OrderLineId, and the SalesOptionId.
Finally, each SalesOption will have data associated with its application to a particular Product/OrderLine. For instance, if the user selects the expediting SalesOption, the user needs to select the level of expediting (NextDay or Urgent); for the giftwrapping SalesOption the user will need to specify the type of giftwrapping.
There are at least two different data designs that would support recording this information. One design is to simply add the required columns to the SalesOptionForOrderLine table: a GiftwrapId column for the giftwrapping option and the ExpediteLevel for the expediting option. For any particular SalesOption most of those columns would be Null; in other words, for the giftwrapping SalesOption the GiftwrapId column will have a valid value but the ExpediteLevel column will hold a Null value.
In the other design, each SalesOption would have its own SalesOptionData table (including ExpediteSalesOptionData and GiftwrapSalesOptionData). Each of these tables would have as its primary key the SalesOrderId and OrderLineId and the columns required by the SalesOption (e.g. the ExpediteSalesOptionData table would have an ExpediteLevel column, the GiftwrapSalesOptionData table would have GiftwrapId column).
There’s no universally right answer here—the right answer will depend on the way the business works. For instance, having a separate SalesOptionsData table for each SalesOption requires me to use one of two data access plans when processing the OrderLines in a SalesOrder.
If the number of OrderLines being processed at any one time is small (such as one SalesOrder’s worth of OrderLines which is, typically, less than six OrderLines), I could live with the performance hit that comes with either data access plan. I could also live with either data access plan if any particular part of the organization only needs to process a small number of specific SalesOptions (if the shipping department only needs to retrieve the information for the Expedite SalesOption). However, if the number of OrderLines being processed at any one time is large ( if I process many SalesOrders at a time) or if there’s a part of the organization that needs to handle all of the SalesOptions applied to an OrderLine then the performance impact could be crippling.
Looking at the business, I can see that any part of the organization will typically only be interested in a few specific SalesOptions applied to a Product/OrderLine combination. The one exception is the order taking application—however, it only works with one SalesOrder’s worth of OrderLines at a time. However, there are several places in the organization where many Orders are processed at once. The shipping department, for instance, prepares a day’s worth of shipping at a time to support combining shipments and reducing costs. That second fact drives me to adding nullable columns to the SalesOptionForOrderLine table.
I admit to having another reason for adding nullable columns to the SalesOptionForOrderLine table. I also know that the amount of data associated with a SalesOption is typically small. If I used individual tables I’d end up with tables that have only one or two columns (other than their primary key columns). I have a visceral objection to that though I’m not sure that I could justify it.
Putting all of this together, it means that, on the data side, adding a new SalesOption consists of adding:
And, of course, adding a new SalesOption requires creating the appropriate role object to hold the code for processing the SalesOption. So that’s next month’s column—the object model.
One of the things that you may have noticed is that I’ve frequently referred to the way that the organization works both in the design phase and in the implementation phase (for me, the implementation phase includes deciding on the details of the design). That’s another one of the assumptions of this column: conditions alter cases. Your deep understanding of how your organization works is critical not only in selecting the right pattern for your application but also in deciding what makes sense when it comes to implementing that pattern. That’s one of the reasons that I appreciate patterns so much: they’re a support rather than a straightjacket and allow me to tailor the solution to my clients’ needs.
One of the comments made by a reader on the original column suggested that the OrderLine should be responsible for managing the SalesOptions rather than the Products. That’s a good question. Certainly, the data design that ties OrderLines to SalesOptions suggests that it’s a reusable choice. But it really leads to more interesting question for this column: What would be the basis for deciding where to put control of the SalesOption role objects? The question is made a little harder to decide because, in this organization, an OrderLine always has a Product assigned to it; disentangling the two business entities is hard to do.
The reason that I felt that the Product should manage the SalesOptions was because of the necessity of validating SalesOptions against the ValidSalesOptionsForProduct table—I assumed would be handled by code in the Product class and that the rest of the SalesOptions code would go in the Product also. However, I’m not sure that’s a compelling argument; The code in an OrderLine class could validate the Product associated with the OrderLine as easily as the Product class could because an OrderLine always knows what Product is associated with it.
One way to make the decision would be to look at the way that the business is run. If, after assigning a SalesOption to a Product/Orderline combination, is it possible to move that Product to another OrderLine in the SalesOrder? Or to change the Product assigned to the OrderLine? If either of those changes is possible, what happens to the SalesOptions? Do the SalesOptions follow the Product to another OrderLine or stay with the OrderLine? If you replace the Product on an OrderLine with a new Product, would it always retain the SalesOptions assigned to the original Product? The SalesOptions stay with the OrderLine, it suggests that the OrderLine is responsible for the SalesOptions.
Another way to answer the question is to look at how the classes will be used elsewhere in the business’ processes. If I know that, somewhere in the organization, Products and SalesOptions needed to be processed even when the Products aren’t associated with an OrderLine then I will have a compelling reason for keeping the responsibility of processing the SalesOption with the Product.
I do have one scenario where Products and SalesOptions are processed independently of an OrderLine: Some SalesOptions have different processing, depending on which Product the SalesOption is assigned to and regardless of the state of the OrderLine involved. For instance, expediting a “thing” is different from expediting a “service”; expediting a thing means shipping it earlier, while expediting a service means the team delivering the service goes to the customer’s site earlier. As a result, when an application asks for the Expediting role object for a Product, different Products will provide a different Role object.
There are other solutions to this problem, of course. For instance, should delivering a thing and a service earlier both be called “expediting”? Having different Strategy objects (one for things and one for services) might also resolve the problem. However, rather than make the OrderLine responsible for determining which Expediting role object to use with a Product or create a complex Expediting role object that can handle all Products, I’ll turn the responsibility for returning the right role object over to the Product.
Peter Vogel is the principal system architect in PH&V Information Services, specializing in SharePoint and service-oriented architecture (SOA) development, with expertise in user interface design. In addition, Peter is the author of four books on programming and wrote Learning Tree International’s courses on SOA design ASP.NET development taught in North America, Europe, Africa and Asia.
Thomas: I had a similar comment on the first article so you're not the first person to mention that. Unfortunately, this column was in the pipeline before the previous column's comment turned up. I'll see what I can do in future columns about providing better graphics.
I would have appreciated if you added diagrams and pictures of all the tables and relations you refer to. As it is now, I get lost in all the references while I get exhausted trying to compile all the information into a mental picture of what it all would look like. Because of that, I fail to see your proposed design. I get the ideas, though.
Elmar: Not a bad idea (and one that I hadn't thought of). I'd worry about what sort of SQL queries I could run against the table and what indexes I could put on the column. However, to make good decisions we'd need to know more about the business: how many rows are going to be in the table, what sort of queries would be run against the column to begin with.
Elmar: That's not a bad idea--hadn't thought of that. I don't know that I have a good reason for not going that route except that I worry that I won't be able to run SQL statements against the column. I'd also worry about my ability to create an index on the column. But to answer either of those questions I'd need to know more about the business to make a good decision (for instance: how many rows should I expect in the table, what sort of searches will be done on the table)
Instead of creating many small tables, or one table with many nullable fields, you might want to consider using a typed xml column. This way each option can record its settings in an xml node. Cheers, Elmar
More MSDN Magazine Blog entries >
Browse All MSDN Magazines
Subscribe to MSDN Flash newsletter
Receive the MSDN Flash e-mail newsletter every other week, with news and information personalized to your interests and areas of focus.