Develop Accommodating Software on
SQL Server 2005 with MetaValue™ Columns
One Usage Scenario for Soft-Coded
Logic™ (SCL™)
by Eugene Asahara©
September 2005
Last Updated: January 2, 2006
Audience:
CIOs and other IT decision makers.
Level: 200
Technologies: .NET Framework, SQL Server 2005
Overview
This article introduces
Soft-Coded Logic (SCL), a Prolog*-based .NET engine I’ve developed, which
modernizes Prolog for the Web Services era. SCL
combines Prolog’s elegant syntax to encode rules that govern actions with the
distributed nature of information (whose infrastructure is Web Services), in
the real world, yielding a powerful development tool. For purposes of
laying out a comprehensive model of how this technology would be deployed in
the retail sector, I describe an SCL usage scenario that takes advantage of the
integration of the Common Language Runtime (CLR) in SQL Server 2005.
*Note: For this article, it is enough to know that Prolog is a
“declarative” language of artificial intelligence (AI) that infers answers from
sets of facts. (For example, if dogs have four legs and a German Shepherd is a
dog, I know, by inference, that the German Shepherd has four legs.) For a
fairly non-technical introduction to Prolog and SCL, please read my article, Introduction
to SCL.
In addition, exercises
using SCL are provided as an introductory to Prolog and SCL.
I call this usage scenario
Accommodating Software. The purpose of deploying Accommodating Software is to
develop or retrofit current software with the flexibility and added
functionality to accommodate the special needs of a company’s customers.
I call the main technique
for implementing this scenario MetaValue Database Columns. MetaValue Database Columns are new SCL
database columns that store small fragments of SCL code for each row, which
describe how to dynamically determine a value, as opposed to simply storing a
static value. Prolog provides an excellent base technology for such an
application, since inference is its specialty. Computer hardware and software
capacities have expanded to the point where the industry is ready to shift more
of the allocation of the growing capacity towards smarter data.
Introduction to the MetaValue
Technique
MetaValues decompose a value stored in a database
table to some extent, into the rules and facts that result in the value producing
flexible answers. MetaValues is one of many
techniques using SCL that result in software that readily accomodates the
unique needs of the many “objects” (individual people, species of plants and
animals, businesses, countries) present in the real world. For example,
rather than store a “Y” in the “Taxable” column for a particular product in a
“Products” table, a fragment of logic code is stored. Whether a product is
taxable or not at any given time may depend on several factors related to the
particular situation. When this fragment of code is executed, the rules are
processed resulting in an end value (in this case “Y” or “N”).
Note: At first glance, this sort of logic would seem more appropriately
placed in middle-tier business objects or even stored procedures. For the
majority of cases, this is true. However, if you think about it, in the real
world, no fixed set of rules will ever fit 100% of situations. There are always
a minority of cases where extenuating circumstances result in our forcing the
principles of that situation into a pigeonhole that doesn’t quite fit. I
include a short essay elaborating of this concept towards the end of this
article under “The Case for MetaValue Database Columns”.
We must rethink what data in
a relational database is. Each row in a database table is the persisted state of
an object. Data in a database is the end value of a series of processes, which
may reflect processes and rules distilled into software architecture and data
assembled from various sources ranging from peoples’ heads to other computer
systems, which has been transformed and merged many times, until it finally
ends up in the nice, tidy matrices of database tables. This data is quickly and
reliably retrieved by a simple query to the database.
The series of processes that
resulted in the value stored in the database may have the appearance of a chain
of events that inexorably lead to a certain destination; one link connected to
the next link. However, that chain is really just one path in a road map of
possibilities. Take a left turn instead of a right turn at any fork along the
way and we end up somewhere different. Likewise, the value obtained by a query
to the database is good only if person seeking the result had the same set of
requisites (set out on the same path) as that which resulted in the stored value
in the database.
Of course, that’s not always
what happens in the real world. In a retail scenario, customers will have
discounts or penalties for reasons ranging from rewards for long-term loyalty,
penalties for poor payment practices, coupons, partnership prices, bulk prices.
If this did represent the final set of possibilities, we could simply encode
these rules. But in our entrepreneurial environment, more rules (and
complications) will undoubtedly come into play.
Figure 1 depicts the history
of how the value of $60 is ultimately set in a database table as the price for
cheese sold to customers of a grocery store.
Figure 1 – How the price for a
product is set.
Remember, it’s not that the
rules shown in Figure 1 are complicated and cannot at some point in time be
distilled into software code. The value of MetaValues is that the rules could
change for some subset. A manufacturer may desire different pricing schemes for
different classes of customers. There could in fact be separate rules for every
customer.
For example, suppose in an
effort to protect small grocery stores against the CostCos and Price Clubs,
The further to the left in
the chain shown in Figure 1, the more disruptive the change. An extreme example
would be if a regulated industry were deregulated (the “USDA pricing laws”
box). The sudden burst of price competition would foster new schemes to lure
customers.
The point of this example is
to illustrate that the malleability of software can be greatly increased if in
the database we stored the RULES for
the last few steps, not some final VALUE
returned that reflects only one set series of events.
In order to write
accommodating software, the rule processing engine would need to:
- Receive
information about the current context. Every decision we make in our daily lives depends upon the context
of our current situation. For example, if I’m invited to a baseball game,
whether I go or not is dependent upon much more than whether I like
baseball.
- Reach
out to other data sources. The
facts we consider when making decisions are rarely all in our heads at the
beginning. We research facts that reside elsewhere, such as in other
peoples’ heads, in books, or on some Internet search engine.
- Robustly
recognize conditions. There is
usually more than one set of conditions that trigger an action. For
example, the action of hopping in my car is triggered by a multitude of
conditions (going to work, to the store, to a baseball game, etc.).
These three attributes play
a part in the following example.
Example - Price Selection
This very simple example
demonstrates how to use MetaValues to select prices for products from a list of
variable possible prices.
The Database
Figure 2 depicts the
Products table with three products listed. Customers could be charged three
different prices (High-end, Regular, Discount), one of which is to be selected
for the point of sale. (Please pardon the breaking of First Normal Form for the
sake of simplicity.)
|
Product ID |
Class |
EnvFriend |
Taxable |
High-end Price |
Regular Price |
Discount Price |
|
Wine |
Luxury |
Y |
Y |
25 |
10 |
5 |
|
Bread |
Staple |
Y |
Y |
9 |
5 |
1 |
|
Prius |
Transport |
Y |
N |
45000 |
22000 |
15000 |
Figure 2 – Products table.
Figure 3 depicts the
Customers table. The PriceSel column is the focus of this example from the
Customers table. This field holds SCL fragments that determine which price to
charge this customer for products in the Products table under given conditions.
|
Customer ID |
Contact |
PriceSel |
IsMonopoly |
Business |
|
FoodCzar |
SCL |
SCL |
Y |
Grocery |
|
Star |
SCL |
SCL |
N |
Retail |
|
Tijara |
SCL |
SCL |
N |
Country |
Figure 3 – Customers table.
There is another SCL column
besides PriceSel: Contact. The SCL fragments stored in this field would
determine which contact would be appropriate for certain conditions. For
example, when I’m invoicing a customer and have a question, I would want my application
to tell me the name of the “purchasing” contact. This field is not part of this
example, but I’ve chosen to show it to demonstrate that SCL fragments can store
rules for more than one column.
The PriceSel SCL Fragments
Each customer can be charged
different prices under different conditions. The rules for selecting the
appropriate price are stored as SCL fragments in the PriceSel column. This
sub-section discusses the PriceSel SCL fragments for the three customers in the
Customers table.
For each of the customers,
the SCL fragment is displayed along with a table summarizing the rules for when
the High-end, Regular, or Discount price is selected. The rules are the “price”
lines under the “CLAUSES” section. The table following the SCL fragment provides
a sentence that reads the rule as a Prolog rule should be read. It may seem
rather verbose, but it’s helpful to sound it out.
Another thing is that the
order of the “price” rules is relevant. The selection of a price is a “scalar”
operation (meaning only an elemental value is returned). It is often the case
that more than one rule may hit (although that shouldn’t happen in this case).
Therefore, there needs to be a way to determine only one rule. In this case,
the first rule that “hits” will supply the value.
Beginning with the simplest,
the first SCL fragment we’ll examine is the price selection rules for the
country of Tijara, depicted in Figure 4. Tijara is a poor country, therefore,
the price selection leans towards lower prices.
Figure 4 – PriceSel for Tijara.
Figure 5 provides a verbose
explanation for the rules encoded in the SCL in Figure 4 for each of the
prices.
|
Price |
Rule |
|
High-end |
There is no rule
defining a condition where this customer will pay a High-end price. |
|
Regular |
The price for the
product is Regular if there exists a fact declaring a regular price for this
product and there is a fact declaring this product is of a Luxury type. |
|
Discount |
The price for the
product is Discount if there exists a fact declaring a Discount price for
this product. |
Figure 5 – Verbose explanations for the SCL rules in Figure
4.
Figure 6 depicts the SCL fragment with
the price selection rules for FoodCzar. The element of whether or not FoodCzar
is a monopoly comes into play, making this SCL is a bit more complicated than
the one for Tijara.
Figure 6 – PriceSel for FoodCzar.
Figure 7 provides a verbose
explanation for the rules encoded in the SCL in Figure 6 for each of the
prices. Notice that there are two rules for selecting the High-end price. This
is “robust recognition”, the third of the three requirements for writing
Accomodating software.
|
Price |
Rule |
|
High-end |
There are two
conditions whereby the price is High-end:
|
|
Regular |
The price for the
product is Regular if there exists a fact declaring a regular price for this
product and there is a fact declaring this product is of a staple type. |
|
Discount |
There is no rule
defining a condition where this customer will pay a discount price. |
Figure 7 – Verbose explanations for the rules depicted
in Figure 6.
Figure 8 depicts the SCL fragment
with the price selection rules for Star. Star utilizes a fact that the previous
two customers don’t have: TaxableProduct.
Figure 8 – PriceSel for Star.
Figure 9 provides a verbose
explanation for the rules encoded in the SCL in Figure 8 for each of the
prices.
|
Price |
Rule |
|
High-end |
The price for the
product is High-end if there exists a fact declaring a High-end price for
this product and there is a fact declaring this product is of a Staple type. |
|
Regular |
The price for the
product is Regular if there exists a fact declaring a regular price for this
product and there is a fact declaring this product is taxable. |
|
Discount |
The price for the
product is Discount if there exists a fact declaring a discount price for
this product and there is a fact declaring this product is of a Luxury type. |
Figure 9 - Verbose explanations for the rules depicted
in Figure 8.
The SCL for Star is more complex. It
includes another type of fact, TaxableProduct, which is followed by some XML.
TaxableProduct is a MetaFact™, which means it describes how to obtain a fact rather than simply
declaring the fact. That description is encoded in the XML that follows it.
TaxableProduct is a “SQL” MetaFact
that queries a SQL Server database to obtain data for the facts. The ability to
reach out to other data sources to obtain facts is one of the three
requirements for writing accomodating code. However, in this case, the database
is the same as the database of this SQL query that will access this SCL
fragment. In other cases, a ConnectionString property can specify another
database, even on another server.
There are other types of “native”
MetaFacts including XML, Web Services, SCL (calling another SCL database, which
opens up intriguing possibilities-- the subject of an upcoming article), and
CLR. In addition, developers can add MetaFact types through an exposed
interface.
Running the Example
The query depicted in Figure 10 will list selected
prices for wine for each customer. During the query’s processing, for each
customer the SCLScalar user-defined function (UDF) is executed. SCLScalar
processes an SCL query against a specified SCL fragment and returns a scalar
value. In this case, that value is a selected price for each customer row.
Figure 10 – SQL query to list the price that each
customer pays for wine.
Note:
The C# code comprising these user-defined functions are provided in Appendix A.
The format of the SCLScalar
UDF is:
dbo.SCLScalar(@query,
@SCL)
The SCL query is the first
parameter of the SCLScalar UDF:
'price(@amount,'+p.ProductID+')'
The SCL query states: Return
the price fact in the @amount variable for the product, p.ProductID.
The second parameter of the
SCLScalar UDF is the SCL fragment that the SCL query is run against. It is
composed the value of c.PriceSel (the SCL fragment from the current customer
row as described in the previous sub-section) and a number of strings resulting
from the execution of several UDFs named dbo.FmtSCLFact1, dbo.FmtSCLFact2,
and dbo.FmtSCLFact3. These three UDFs
append facts with one, two, or three arguments, respectively. An example of the result of the resolution of this parameter is
depicted in Figure 11. This happens to be the SCL to select a price for wine
for FoodCzar.
PREDICATES producttype(product,type) price(amount,product) priceoption(amount,custtype,product) IsMonopoly(YN) CLAUSES price(@price,@product) :- priceoption(@price,High-end,@product), producttype(@product,Luxury). price(@price,@product) :- priceoption(@price,regular,@product), producttype(@product,Staple). priceoption(25,High-end,wine). priceoption(10,regular,wine). priceoption(5,poor,wine). producttype(wine,Luxury). productID(wine). IsMonopoly(Y).
Figure 11 – SCL fragment containing rules and facts to
select a price for wine for FoodCzar.
The six facts shown in Figure
11 in bold/italic are the facts generated by the six
“dbo.FmtSCLFactx” UDF calls. The six facts represent information about the
particular context. This is the first of the three requirements for writing accommodating
code. In this case, it supplements the PriceSel SCL for each customer with
facts about the product for which we’re selecting a price and other facts that
the PriceSel SCL may lack.
Note: Because we are selecting only one product, it would have been
optimal to format the facts particular to the product once and concatenate the
resulting string to the SCL. In this way, the dbo.FmtSCLFactx calls are called
for each row. IsMonopoly would still need to be called for each customer row
since c.IsMonopoly is a field of the customer.
Notice that IsMonopoly from
the customer table is passed as part of the SCL, but TaxableProduct from the
Product table is called as a MetaFact. A fact should be passed in the SQL as
IsMonopoly if it’s expected that the fact will be used a significant percentage
of the time. A fact should be a MetaFact is it’s unique to the customer and
will probably not be used again. The advantage of the former is that it’s much
faster than processing a MetaFact. The advantage of the latter is that the
overhead of submitting that field for each customer (that will probably not use
the field) is avoided.
Following the SCLScalar call
in the SQL from Figure 10 are three fields, p.High-endPrice, p.Price and, and
p.DiscountPrice. These fields are included just so it is easy to see which
price was chosen.
Figures L and M show the
result sets returned by running the SQL from Figure 10 for wine and for bread,
respectively.
|
CustomerID |
Selected Price |
High-end Price |
Regular Price |
Discount Price |
|
Star |
10 |
25 |
10 |
5 |
|
FoodCzar |
25 |
25 |
10 |
5 |
|
Tijara |
10 |
25 |
10 |
5 |
Figure 12 – Selected prices for wine, a luxury.
|
CustomerID |
Selected Price |
High-end Price |
Regular Price |
Discount Price |
|
Star |
5 |
9 |
5 |
1 |
|
FoodCzar |
9 |
9 |
5 |
1 |
|
Tijara |
1 |
9 |
5 |
1 |
Figure 13 – Selected prices for bread, a staple.
Figures 14
and 15 show which rules were engaged for each customer for each product.
|
CustomerID |
Wine, a Luxury |
|
Star |
price(@price,@product) :- priceoption(@price,discount,@product), producttype(@product,Luxury). |
|
FoodCzar |
price(@price,@product) :- priceoption(@price,High-end,@product), producttype(@product,Luxury). |
|
Tijara |
price(@price,@product) :- priceoption(@price,regular,@product), producttype(@product,Luxury). |
Figure 14 – Rules engaged for wine.
|
CustomerID |
Bread, a Staple |
|
Star |
price(@price,@product) :- priceoption(@price,regular,@product), TaxableProduct(@product). |
|
FoodCzar |
price(@price,@product) :- priceoption(@price,High-end,@product), producttype(@product,Staple), IsMonopoly(Y). |
|
Tijara |
price(@price,@product) :- priceoption(@price,discount,@product. |
Figure 15 – Rules engaged for bread.
To demonstrate how
MetaValues consider the latest information, Figures N and O depict the Products
and Customers tables, respectively, after making two changes: Wine is no longer
taxable and FoodCzar is no longer a monopoly.
|
Product ID |
Class |
EnvFriend |
Taxable |
High-end Price |
Regular Price |
Discount Price |
|
Wine |
Luxury |
Y |
N |
25 |
10 |
5 |
|
Bread |
Staple |
Y |
Y |
9 |
5 |
1 |
|
Prius |
Transport |
Y |
N |
45000 |
22000 |
15000 |
Figure 16 – Products table after making wine non
taxable.
|
Customer ID |
Contact |
PriceSel |
IsMonopoly |
Business |
|
FoodCzar |
SCL |
SCL |
N |
Grocery |
|
Star |
SCL |
SCL |
N |
Retail |
|
Tijara |
SCL |
SCL |
N |
Country |
Figure 17 – Customers table after making FoodCzar not a
monopoly..
Running the SQL in Figure 10
will for wine and bread will result in Figures P and Q, respectively.
|
CustomerID |
Selected Price |
High-end Price |
Regular Price |
Discount Price |
|
Star |
5 |
25 |
10 |
5 |
|
FoodCzar |
25 |
25 |
10 |
5 |
|
Tijara |
10 |
25 |
10 |
5 |
Figure 18 – Selected prices for wine, a luxury that is
now not taxable.
|
CustomerID |
Selected Price |
High-end Price |
Regular Price |
Discount Price |
|
Star |
5 |
9 |
5 |
1 |
|
FoodCzar |
5 |
9 |
5 |
1 |
|
Tijara |
1 |
9 |
5 |
1 |
Figure 19 – Selected prices for bread, a staple, where FoodCzar
is no longer a monopoly.
With wine, a luxury item,
now non-taxable, Star now pays the Discount price rather than the Regular
price. The second rule shown in Figure 8 is engaged:
price(@price,@product) :-
priceoption(@price,discount,@product),
producttype(@product,Luxury).
Note: If the result of this rule for Star doesn’t quite make sense, I
purposely made it this way to demonstrate that rules in the real world can
sometimes seem very arbitrary and one-off. Perhaps Star ends up paying less for
Luxury items when it is not taxable because it’s the result of some compromise
related to another incident.
With FoodCzar
no longer a monopoly, FoodCzar now pays the Regular
price for bread, a staple, rather than the High-end price. The third rule in Figure
6 is engaged:
price(@price,@product) :-
priceoption(@price,regular,@product),
producttype(@product,Staple).
The Rules Have Changed for One
Customer: The
killer Aspect of SCL/MetaValues
As fun as this exercise may
be so far, the following example is intended to drive home the fact that the
real value of the MetaValue technique isn’t so much when a value involved in
the logic changes, but when the logic
itself changes. If rules change for one customer, we must somehow reflect
those rules or our ability to adequately serve that customer will be impeded.
Remember too, it’s also not just a matter of losing one customer. Potentially,
a significant percentage of customers may eventually require such special
handling.
Suppose the country of
Tijara is now quickly developing, but due to a still corrupt government, Tijara
fluctuates wildly between destitute and acceptable conditions. Therefore, after
years of debate in Congress, it is decided that when the exchange rate between
Tijara’s currency and the US dollar is less than ten to one, Tijara must pay
the High-end price for Luxury items and not the Discount price. As a result,
the PriceSel SCL originally shown in Figure 4 is updated as shown in Figure 20.
Figure 20 – Updated PriceSel for Tijara accomodating
their positive development.
Modifications are in bold.
Three items were added to
Tijara’s PriceSel SCL:
- A predicate for an ExchangeRate fact, which in
this case is a MetaFact.
- A new rule: The price for the product is High-end
if there exists a fact declaring a High-end price for this product and
there is a fact declaring this product is of a Luxury type and the
exchange rate between the Tijara Peso and the US Dollar is less than ten
to one.
- Notice that the lessthan term of the rule
executes an SCL query to obtain the ExchangeRate.
- The ExchangeRate MetaFact definition. This
MetaFact obtains the exchange rate by querying the XML file. Current.xml,
at the specified URI and executing the specified XQuery.
Using MetaValues, Tijara
could be served without modification to any application binaries. In addition,
the rules for Tijara do not hinder the service for any other customers.
The Case for MetaValue Database
Columns
The opportunities for implementing
SCL are countless, but since this article is just an introduction, I’ve chosen
to focus on the implementation of MetaValue Database Columns. To recap,
MetaValue Database Columns can help a company’s software better accommodate
each customer’s unique needs in this current business climate where the
rigidity of most software applications results in frustrated customers.
Software as it is normally
architected today is too rigid for the scale of users it must support to achieve
high levels of customer satisfaction.
One Size Really Doesn’t Fit All: Accommodation Makes a Better Fit
Hardly a day goes by when I,
as a software user, must conform to a frustrating lowest common denominator
rule imposed by an inflexible computer program. A software program is a product
like any other. Software development is also fraught with constraints of time
and scope, so a project manager must make sacrifices and know that ending up
with a product that serves everyone to 100% satisfaction is highly unlikely.
Most software, for example,
is built and marketed to the select group. But within that group, members fit
the needs profile of the intended target, on a sort of continuum to varying
degrees. On one side are the customers who are a perfect fit and on the other
side customers who become less and less of a fit. If a company is to grow, one
of the fundamental tasks is to increase its customer base by making a better
fit.
The idea isn’t that every
single customer will necessarily require custom rules that govern a company’s
relationship with that customer. In fact for most customers concerning
situations that do not particularly interest them, the “standard package” would
do fine and they wouldn’t have to think much further about it.
Meeting customers’ differing
needs and requirements with the same software is at the heart of accommodating
software.
Information Overload
Hard drive capacity is
outpacing
There are two forms of
information overload. The first is just the sheer volume of information. It was
mind boggling only five years ago to imagine that it would take a whopping
seven terabytes to map the earth to our specifications. Seven terabytes is common
now. The second is the corollary that all this information overwhelms our
decision making choices.
Related to software, we are
always presented with lists of things to choose from. For example, if we look
up a contact in our Customer Relationship Management software, we may get a
short list of phone numbers for this contact (home, cell, desk, fax, etc). We
may quickly select “desk” first, then cell, then home. But how do we determine
the order? How do we determine if it’s sufficient to just call the contacts
desk and leave a message? How do we determine if we should call the contact at
home? That may be a touchy decision.
Simple choices such as those
just mentioned aren’t in themselves much. But what about the hundreds or
thousands of other such choices we make every day? So, how do we have it both
ways? How do we make so many complicated personal choices (and be thankful for
the flexibility) and at the same time not spend our entire day making such
decisions? We can delegate many of these tasks to computers as we’ve already
done with mundane tasks.
Dynamic Data Mining under Evolving
Conditions
A type of information
overload occurs when a data mining application can’t accommodate users’
changing or varying needs. CRM software can benefit tremendously by
implementing SCL. In a restaurant scenario, for example, both the business
owner and its customer would see an increase in user satisfaction-- the owner
by increasing sales, and its customer through better service.
Take my beverage choices at
my favorite restaurant: I don’t order the same drink on every occasion. An
enterprising restaurant using data mining software would rank the drinks I’ve
purchased over time. It may store in my record on their database that I’ve
ordered Diet Coke 80% of the time. Therefore, a waiter that I hardly know may
ask to my astonishment, “Diet Coke for you, sir?” (He would know who I am from
my OpenTable reservation.) A restaurant’s data mining may even be sophisticated
enough to go a level deeper and find I order Diet Coke 95% of the time when I’m
there during lunch.
However, the drink that I
order (or most other things I do for that matter) is based not on statistics,
but on rules dependent upon characteristics of the current context. Examples of
such rules are:
- I order a Diet Coke when I’m eating lunch with a
business associate, but never for dinner (too late for caffeine).
- I order wine when I’m having dinner with my wife
and I’m not driving and my wife doesn’t wish to drink either.
- I order beer when I’m out with buddies on Friday
after work and I’ve had less than two and it’s more than an hour before I
leave.
Contexts change. Complex
logic, once vetted can be coded with C#, T-SQL or many existing software
development platforms. But, like all scientific experiments, they have the same
result only when all of the conditions are the same. In real life, two
situations are rarely the same because conditions for which we are not aware of
are different.
More to Come
There is certainly way more to this
story. I begin here because I think this is the most readily accessible usage
scenario. As a Lead Database Architect at Microsoft, I also wish to present a
great example for CLR integration in SQL Server 2005. Hopefully, this will help
light the way for more creative usages for this major feature in SQL Server
2005 opening new markets for software development.
Over the following weeks, I will
publish articles on other topics including:
- More on the SCL language
- More usage scenarios
- Performance optimization
- Features Roadmap
- More samples
Appendix
Appendix A
Appendix
A contains further a walkthrough on the C# code that makes up the CLR
user-defined functions and stored procedures loaded into SQL Server 2005.
Appendix B
Appendix
B contains further SQL Server 2005 examples.