Обсуждение: some howto/theory book/tutorial on practical problem solving in SQL

I'm looking to some book/tutorial/sample code that will teach me how
to use SQL to solve some standard problem that goes a bit beyond
using group by and aggregates.

Something like "SQL problem solving" or "SQL design strategies for
selected problems".

My current problem is how to manage discounts in SQL, inside
transactions. Specifically how to "delete" promotions if they are
overlapping, considering I have to display discounted prices on a 1M
article DB and I may have hundreds of promotions running and they
may involve even 10% of the catalogue. But this is just the
beginning.

I bet I'll have different set of problems later.

Online tutorial/howto or reference to Open Source programs that
really exploit SQL (and not python, php, ruby) to deal with discounts
would be very welcome.

eg. I did find very educational to look in the code of GNUMed in the
past.
Actually a reference to some Open Source software that does discount
management at the DB level would be a very good pointer.
And of course I'm using postgresql and even if DB agnostic
techniques will be welcome I don't need to be DB neutral, I'm
working on postgresql.

I skimmed through Celko books and at the moment they seems the
nearest thing to what I'd like to learn even if too much
"theoretical" at the moment. O'Reilly "SQL cookbook" is another
example of the kind of stuff I'm looking for... but the examples are
more like ingredients then recipes.

thanks


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Sun, Jan 11, 2009 at 9:32 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
>
> My current problem is how to manage discounts in SQL, inside
> transactions. Specifically how to "delete" promotions if they are
> overlapping, considering I have to display discounted prices on a 1M
> article DB and I may have hundreds of promotions running and they
> may involve even 10% of the catalogue. But this is just the
> beginning.
>
> I bet I'll have different set of problems later.

Well, if you can't find a book that deals with it, I'm betting someone
on the list will understand the issue and help out. :)

> I skimmed through Celko books and at the moment they seems the
> nearest thing to what I'd like to learn even if too much
> "theoretical" at the moment. O'Reilly "SQL cookbook" is another
> example of the kind of stuff I'm looking for... but the examples are
> more like ingredients then recipes.

I highly recommend Celko's SQL books.  They may seem too theoretical,
but they have a lot of good information I found myself reusing all the
time when I first started out.  I was a little disconcerted by his
resemblence to Anton Lavie (sp) at first though.

On Sun, 11 Jan 2009 11:19:19 -0700
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:

> On Sun, Jan 11, 2009 at 9:32 AM, Ivan Sergio Borgonovo
> <mail@webthatworks.it> wrote:

> > My current problem is how to manage discounts in SQL, inside
> > transactions. Specifically how to "delete" promotions if they are
> > overlapping, considering I have to display discounted prices on
> > a 1M article DB and I may have hundreds of promotions running
> > and they may involve even 10% of the catalogue. But this is just
> > the beginning.

> > I skimmed through Celko books and at the moment they seems the
> > nearest thing to what I'd like to learn even if too much
> > "theoretical" at the moment. O'Reilly "SQL cookbook" is another
> > example of the kind of stuff I'm looking for... but the examples
> > are more like ingredients then recipes.

> I highly recommend Celko's SQL books.  They may seem too
> theoretical, but they have a lot of good information I found
> myself reusing all the time when I first started out.  I was a

OK... let's be more clear... they seems the books I'd surely read if
I had 1 month to find a proper solution to my problems and become a
better programmers for the years to come.
Actually from a quick glance they are definitively more practical
than a book on sets algebra ;) and among all the books I had a
chance to skim they really look the ones that could be useful and
they have a good chance to find a permanent place in my bookshelf.

Just not to misguide people.

If I had other 3 months I'd pick up Date's book.

I just remember now I gave a look to a .*postgres.*ecommerce book
but while it seems to me it could be a good book for learning some
practical postgresql and start to know the tool it was not the kind
of book that I'd title "how to solve this in SQL".

I just saw there is a "Sql Puzzles" by Celko that looks promising.
I was able to see the first 2 puzzles on Google books.
Now I'm downloading all the Open Source ecommerce apps I could
find... but having done this before I think none of them will really
exploit the DB and will rely mostly on php.
I think Open Source accounting [1] programs may have more chances to
teach me something about the problem I'd like to solve since they
tend to be less mysqlish.
If anyone know an accounting Open Source program that really exploit
postgresql as an engine I'd be glad to look at the source.

> little disconcerted by his resemblence to Anton Lavie (sp) at
> first though.

http://images.google.com/images?q=Joe+Celko

http://en.wikipedia.org/wiki/Anton_Szandor_LaVey

I think I'm going to read some Teach yourself SQL in 1fs by Laura
Lemay if I want to keep sleeping at night or I may enrol myself in
Milingo's army.

If I had eternity I wouldn't be so worried about performances so I
think I could finish War and Peace.

I just found an answer by Celko to a problem similar to mine here:
http://www.eggheadcafe.com/software/aspnet/31256828/calculate-discount-by-cat.aspx

[1] accounting? just a tool that deals with discounts and an
inventory. What would be the right term to search for in google?

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Jan 11, 2009, at 8:32 AM, Ivan Sergio Borgonovo wrote:

> I'm looking to some book/tutorial/sample code that will teach me how
> to use SQL to solve some standard problem that goes a bit beyond
> using group by and aggregates.
>
> Something like "SQL problem solving" or "SQL design strategies for
> selected problems".

O'Reilly's SQL Hacks is a good one that fits the bill you describe.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

On Tue, 13 Jan 2009 22:18:32 -0800
Erik Jones <ejones@engineyard.com> wrote:

> 
> On Jan 11, 2009, at 8:32 AM, Ivan Sergio Borgonovo wrote:
> 
> > I'm looking to some book/tutorial/sample code that will teach me
> > how to use SQL to solve some standard problem that goes a bit
> > beyond using group by and aggregates.
> >
> > Something like "SQL problem solving" or "SQL design strategies
> > for selected problems".

> O'Reilly's SQL Hacks is a good one that fits the bill you describe.

Thanks.

I think it is complementary to Celko's SQL puzzles.
O'Reilly's book seems techniques on the field.
Celko's book seems design on the field.

Elsevier seems to make it difficult to buy ebooks in a convenient
way.
I think I'm going to buy 'SQL hacks'. It still seems a good
investment.

Meanwhile I downloaded all the ecommerce, billing and ERP Open Source
programs I know (over 60) and started to examine the code to see
how they manage discounts in SQL.

The one that seems more promising to learn from seems:
ofbiz
webERP
promogest

You can find the most flexible discount management in the ecommerce
programs but they do it in the client language (php) rather than in
sql and most (all?) the time in a way that's not compatible with a
1M items catalog and that is not atomic when you close the order.

$result=db_query("select [some items from catalogue]");
while($row=db_fetch_array($result1) { //some logic $result2=db_query("select [some extra data about item]"); //compute
discount

The billing/ERP programs have a more B2B approach so it seems that
marketing is not among their priorities.
They end up in having stuff like
create table prices( itemid int references items (itemsid), discountclass int references discountclient (classid),
pricenumeric
 
)

But no way to mass apply/revoke overlapping discounts.

I'll come back asking comments on a possible design to solve my
problem later.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it