Developer Forum

I'm trying to figure out a good way of representing any kind of
discounts. I.E., you have a bunch of consumers with various
attributes (age, student status, income, previous history of
purchases, etc.), and you have producers who want to offer discounts
based on any combination of these attributes. It seems to me that
this must involve storing queries in a relation: e.g., you could have
two columns: the amount of the discount, and the query that selects
the people to apply this discount to.

But what is the best way of storing a query in a particular relation?
And what are the security implications of doing this----and how do you
run these queries once you get them? Or, can anyone think of a better
way of doing this?

Thanks,

Nick.

Never store queries. Have a table with a list of attributes and two
link tables - one to link the attribute to the user and one to link the
attribute to the discount.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

How would you trigger discounts based on more general criteria in this
scheme? E.G., I want to offer discounts to people with income under
$35,000 and age over 65. Another producer may want to offer discounts
over 60, under $20,000. Etc.

nchubrich:

[...]

That would be one of many possible ways, yes. But it depends on the
situation.

1.
Who is going to manage the discounts? Is it the application
administrator, who happens to be able to build, test and implement SQL
queries? Or do you want a web interface, where authorized suppliers
manage their own discount rules?

2.
How many discount rules will you have? How often will they change?
Would it be sufficient to have a Discount Object in your application,
that manages each discount rule in an specific
Supplier-Discount-Object? That way, you could build an appication
discount 'machine' with discount functions. The queries would be in
your application, perhaps using prepared statements.
New discount rule? You would add a new 'discount function' to your
application.

3.
How complex will the discounts be? What about
"20% discount for students under 21, unless they still live with their
parents; additionally 5% discount if more than 3 purchases in the past
6 months on the same delivery address, but only if these purchases sum
up to $1000+. No discount for Arizona and Wisconsin, though. Oh, the
discount is not given on P&P, and also not for batteries. This rule
applies on even days in March, only."


--
Erick

>I'm trying to figure out a good way of representing any kind of

I believe that this is not the normal way of dealing with discounts.
Discounts are granted to an *ORDER*, not necessarily a person.
(So you might have a standard discount of 5% off any order over
$1000 regardless of who the person is, but it doesn't combine
with other discounts.)

You would normally run a query, using order attributes and
person attributes to determine what discount(s) are available, and
then determine which are applicable (many times only one discount
can be used at a time).


When would you use such a query? You want to figure out which
discounts are applicable to *THIS ORDER*. Past runs of the query
may be obsolete, as past purchasing history, possibly age, etc.
will be different for this order from the last one.



I would avoid it. It is possible to store queries in text fields.


Anything you can get as a text string can be run as a query.


Almost anything is an improvement.

You can have a table of requirements for discounts, with minimum past
purchases, student status, income requirements, etc. and select the
rows for discounts which might apply.

chubrich wrote:
> How would you trigger discounts based on more general criteria in this
> scheme? E.G., I want to offer discounts to people with income under
> $35,000 and age over 65. Another producer may want to offer discounts
> over 60, under $20,000. Etc.
>

<Top posting fixed>

Have the appropriate entry in your attributes scheme - you could have
two attributes such as "income under $35K" and "age over 65" (which is
more flexible), or you could have a single attribute such as "income
under $35K and age over 65". The former is more flexible, the latter
easier to program, especially if you have more complicated AND and OR
logic in your tables, i.e.

"(income under $35K AND age over 65) OR
(income under $20K and age over 60").

To take a simple case (only AND logic), your discount table could have
something like:

discount_id attribute_id comparison value
1 1 less_than 35000
1 2 greater_than 65
2 1 less_than 20000
2 2 greater_than 60

Where attribute_id 1 is income and attribute_id 2 is age. (Actually,
I'd probably have comparison as an ID, also). Then use application
logic to build your queries. You may have to add to the application
logic early on, to handle discount criteria you hadn't previously
considered (i.e. visited XYZ Ski Slopes at least 5 times last year), but
this should handle all but the most complicated sets of discounts.

You could extend this to have an attribute of "age greater than" and
another of "age less than", and get rid of your "comparison" column
completely. Easier logic, but more entries in your tables.

You could even put most or all of the logic in a stored procedure and
let it compute the discount for you.

Another way I used in the past with a game I was working on needed to be
much more complicated. This had hundreds of attributes for various
things, and these attributes had to be adjustable, and often compounded
(i.e. character strength went down if the character was hungry and/or
had fought someone recently but up with armor). I ended up fetching all
appropriate attribute id's for the character into an array in the
application. Then at the appropriate places in the code, the
application checked for the applicable entries. There was a lot of code
involved (there needed to be), but I was dealing with a fairly closed
set of attributes so little code needed to be changed when attributes
changed.

Side note: Unfortunately, the person who wanted this game had
unrealistic expectations (thought it would make him a millionaire). It
was a good idea, but people wouldn't pay what he was asking to play it.

There are other ways, but these two are flexible and not that hard to code.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

Yup. The approach is fiendishly powerful, and about as economical as you
can get without getting to the point of actually writing a language
parser. There are whole general-purpose non-SQL query tools that build
on exactly that kind of structure.

--
Cunningham's First Law:
Any sufficiently complex deterministic system will exhibit
non-deterministic behaviour.

hank you all for your help!

Erick---

I really would like to plan for the worst possible case: i.e. non-
administrators entering discounts, many discount rules that change
much, and potentially complicated discounts like you made up in 3).
And what's the fundamental difference between storing a query in an
application, and in the database itself?

Gordon---

"You would normally run a query, using order attributes and
person attributes to determine what discount(s) are available, and
then determine which are applicable (many times only one discount
can be used at a time). " So how do you store this query, and connect
it with the discount (for instance, 5% off for orders > 100, 10% off
for ages < 25, etc.)

Peter---

Any links to these query tools?

Jerry---

I see how you could make attributes this way, but I am bothered by the
way this turns a single number into a potentially infinite number of
attributes. Although people don't normally grant discounts based on
being age 31.415, I don't want to pre-conceive things.

I was originally trying to develop a scheme something like what you
showed me, and then I realized....there already \is a way of
specifying things that satisfy arbitrary combinations of attributes,
and more; and it's called SQL! I did not want to have to re-invent
SQL itself; at the same time, I could not help feeling I was going
against the grain by trying to use SQL in a way that it was seemingly
not intended for.

Hence my questions----and as you can see, I am fairly new to SQL.

(By the way, I am using something called ClojureQL, which assembles
queries using a Lisp-type notation. This may give me other options,
especially of the kind that Erick mentioned, doing it in the
application level.)

Everyone seems to advise against storing queries. Does this mean not
to use stored procedures, or is it specifically against storing
queries in tables? What happens if one \does use queries in this way:
security/inefficiency problems? And, if one uses stored procedures,
how are these connected with particular discounts?

I hope this isn't asking \too \many questions.....

Nick.

On Mar 10, 11:42m, "Peter H. Coffin" < XXXX@XXXXX.COM > wrote:

chubrich wrote:
> Thank you all for your help!
>
> Jerry---
>
> I see how you could make attributes this way, but I am bothered by the
> way this turns a single number into a potentially infinite number of
> attributes. Although people don't normally grant discounts based on
> being age 31.415, I don't want to pre-conceive things.
>
> I was originally trying to develop a scheme something like what you
> showed me, and then I realized....there already \is a way of
> specifying things that satisfy arbitrary combinations of attributes,
> and more; and it's called SQL! I did not want to have to re-invent
> SQL itself; at the same time, I could not help feeling I was going
> against the grain by trying to use SQL in a way that it was seemingly
> not intended for.
>
> Hence my questions----and as you can see, I am fairly new to SQL.
>
> (By the way, I am using something called ClojureQL, which assembles
> queries using a Lisp-type notation. This may give me other options,
> especially of the kind that Erick mentioned, doing it in the
> application level.)
>
> Everyone seems to advise against storing queries. Does this mean not
> to use stored procedures, or is it specifically against storing
> queries in tables? What happens if one \does use queries in this way:
> security/inefficiency problems? And, if one uses stored procedures,
> how are these connected with particular discounts?
>
> I hope this isn't asking \too \many questions.....
>
> Nick.

<Top posting fixed>

You need to perform the comparison someplace, don't you? If you need to
compare against an age of 31.415, that figure and the test needs to be
stored, and a query built using it (or the application program has to do
the figuring).

And will your non-technical administrative person be able to create SQL
queries on the fly? I suspect not - which is one good reason for not
having SQL queries in the database.

Stored procedures are not just SQL queries - they are execution units
which may consist of a single query, but most often do a lot more than
the one query. And they are protected from being altered - unlike text
string queries stored in your database.

Plus, if your database changes, you need to go through and potentially
alter every query. For instance, if you change a column name, it could
affect every query in your database. By proper structure of your
application code, you can limit the changes to just a few places. For
instance, I do a lot of OO programming, and have table access classes.
These classes do all the work related to their respective tables; the
rest of the application doesn't have any SQL in it.

Of course, if your database changes, you have to potentially change your
SP's, also. But that's typically a much smaller job as one SP can do
the work of multiple individual SELECT statements.

Hope this is a bit clearer.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
XXXX@XXXXX.COM
==================

gt;"You would normally run a query, using order attributes and

You have a discounts table with qualification columns listed for
each attribute. The query (which is part of the application) checks
which discounts the order is qualified for and returns a list of
them. Another column gives the amount of the discount. Realistically,
I think you'd need another column or several for what the discount
applies to.

Note that you have to modify the application and the database schema
if you add any new attributes to check, but you have to modify the
application and schema *ANYWAY* to store what you're comparing against.
(and potentially you have to call up all existing customers and ask
them what their shoe size is).

Example: the attributes involved are age (minimum and maximum),
previous order total (minimum and maximum), and this order total (minimum).
You might have a discount table like this:

discount table
discid min_age max_age min_ordr max_ordr min_curorder discount comment
1 65 null $100 null $10 5% Senior discount
2 null 18 null null $1 3% Child discount
3 null null null null $500 10% Large order discount
4 null null null $0.01 $1 10% First time buyer discount

min_ordr and max_ordr refer to *PAST* order history.

min_curorder refers to the *current* order.

null in a qualification column means everyone qualifies.

All of the qualification columns are ANDed together. If you want OR,
add more rows.

The minimum and maximum fields allow you to avoid having separate columns
for each possible threshhold (age 18, 21, 25, 30, 40, 50, 65, 100, etc.
age can be a fixed-point number if you want.)

You need to decide whether an exact equal match qualifies or not
(here I assume it matches on minimum, but does not match on maximum)
and write the WHERE clause appropriately.

Your WHERE clause might look like this:

WHERE ((person.age >= discount.min_age) or (discount.min_age is null))
AND ((person.age < discount.max_age) or (discount.max_age is null))
AND ((person.orderhistory >= discount.min_ordr) or (discount.min_ordr is null)
AND ((person.orderhistory < discount.max_ordr) or (discount.max_ordr is null)
AND ((order.ordertotal >= discount.min_curorder) or (discount.min_curorder is null)

Note that to add a had_sex_with_boss discount, you have to somehow
add and populate a person.had_sex_with_boss field, a person.sex
field, add a discount.had_sex_with_boss field ((abbreviated SWB)
with 3 values: null = don't care, Yes = match if had sex with boss,
No = match if didn't have sex with boss), add a discount.sex field
(with 3 values: null = don't care, M = males only, F = females
only.)

discount table
discid min_age max_age min_ordr max_ordr min_curorder SWB sex discount comment
5 18 30 null null null Yes F 50% Hottie young women who had sex with boss get big discount
6 65 null null null null Yes F -20% Old women (e.g. ex-wives) who had sex with boss get charged more
7 null null null null null Yes M -200% Old cellmates who had sex with boss get charged a LOT more
8 null 18 null null null Yes null 99% Underage kids boss has had sex with get REALLY big discount

You aren't done yet. You need to decide which discounts apply if
the above rules yield more than one possible discount. One easy
(but not necessarily what was intended) rule might be that discounts
do not combine, but a customer gets the biggest discount they'r

nchubrich:


Hi Nicholas,
As Jerry has pointed out, it would be well received if you'd be so kind
to reply according to these guidelines:
http://www.yqcomputer.com/
Thank you!


The difference is in your data model.
As Gordon pointed out, if you have an entity 'Discount', you will
probably want it to be related to some other entities in your model.
Gordon suggests 'Order' (in what cardinality would that be?), but I'm
not sure. For instance: an Order usually has OrderLines (one for each
ordered Article) and Discounts may be related to single OrderLines, not
to the whole Order.

Due to the nature of discounts (I don't think a Discount is an entity
in your data model at all), I see discounts not as data, but as a
result of a process, that is performed whenever an order (or order
line) is to be produced. That's why I tend to develop functions (or
object methods, or whatever type of programming you're doing) to
calculate discounts, based upon rules and parameters/variables. The
parameters and variables may come from your database (since it's data),
but I'm having a hard time storing rules in a relational database.


Now that I'm talking about rules and variables, would you still want
non-admins to manage both in your system? Wouldn't it be possible for
your suppliers to "send in" their request for discount registration,
and have an application person manage them in your system?
It would perhaps still be possible for suppliers to manage the
_variables_ (i.e. change 5% to 7%), but the _rules_ (i.e. "this
discount applies if the customer is under X years old, lives in ABC and
has at least NNN orders this year") are something different.

--
Erick

None that will help with MySQL, but the as/400 (now IBM i) freebie
query/400 tool uses exactly this kind of thing to build its record
selection criteria.

And the 400 is INSANELY database-oriented. Classically it has tables
instead of FILES even though it calls them files, and schemas (called
Libraries) that hold them, views over (one or more) tables called
"logical files", an almost-frustrating awareness of character sets and
collations built into the OS and converts between them transparently and
on the fly. Even the source code files are database tables, with columns
for the actual code, the sequence within the whole, and the date that
the line was last updated. THAT comes in handy way more often than one
likes to admit...

--
61. If my advisors ask "Why are you risking everything on such a mad
scheme?", I will not proceed until I have a response that satisfies
them. --Peter Anspach's list of things to do as an Evil Overlord

I am completely unfamiliar with the system, but I would like more
information about those source tables. Any suggestions or references?