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.

nchubrich wrote:
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.
jstucklex (AT) attglobal (DOT) net
==================

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.

On Mar 9, 8:16*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

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.

nchubrich wrote:
<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.
jstucklex (AT) attglobal (DOT) net
==================

On Wed, 10 Mar 2010 07:26:29 -0500, Jerry Stuckle wrote:
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.

Thank 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:42*am, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:

nchubrich wrote:
<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.
jstucklex (AT) attglobal (DOT) net
==================

On Wed, 10 Mar 2010 14:00:57 -0800 (PST), nchubrich wrote:
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

On Mar 11, 3:47*pm, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
I am completely unfamiliar with the system, but I would like more
information about those source tables. Any suggestions or references?