SAP HANA: Customer history

Question

I have different tables with customer Id, items bought with a date of purchase.

books table consists of customer id, bookNr and date of purchase.

shoes table consists of custoemr id, shoeNr and date of purchase.

.

we have like for every item a table.

If the customer buys an Item(x) lets say a book with bookNr someNumber. I would like to retrieve all the orders in all the tables before the date of purchase of bookNr someNumber for all the customers.

Any suggestions how to design the query and the best way to represent the data.

Lars Br. · Accepted Answer · 2017-05-31T01:52:13.313

The way you designed the tables mixes two concepts (type of item that got purchased and the purchase itself). This leads to the difficulties you now face. An alternative approach would be to have a PURCHASES table that links to an "item" table. In the "item" table you could differentiate what item it is and, depending on how different the items are, link to further detail tables, that are specific to the item type.

This could be a DB design that looks like this:

  [PURCHASES] -- [ITEMS] -- [BOOK_ITEM_DETAILS]
                    |
                    +-------[SHOE_ITEM_DETAILS]

If you want to keep your current design, the probably easiest approach here would be to create a view that combines (unions) the different item tables. E.g.

 CREATE view CUSTOMER_PURCHASES as
 SELECT customer_id, shoe_Nr as item_nr, date_of_purchase FROM shoes
 UNION ALL
 SELECT customer_id, book_Nr as item_nr, date_of_purchase FROM books
 ...

That way, all purchases are available in a single view.

Now, if you look for all items purchased by a user before that same user bought a specific item (e.g. a shoe), you can use a join query.

SELECT customer_id, item_nr, date_of_purchase
FROM customer_purchases cp 
left outer join 
     (SELECT customer_id, item_nr, date_of_purchase
      FROM customer_purchases
      where item_nr = <shoe_item_nr>
      and date_of_purchase = <date_of_purchase>
      and customer_id = <customer_id>) as ref_purchase
on 
    cp.customer_id = ref_purchase.customer_id
and cp.date_of_purchase < ref_purchase.date_of_purchase;

I extended my answer with an example for finding the earlier purchases. — Lars Br., May 31 '17 at 01:52
`cp.date_of_purchase < ref_purchase.date_of_purchase;` this was the trick. i didnt got the idea of using anyother operator other than `=` in joins. Thanks Lars! I have learned something :) — sveer, Jun 01 '17 at 08:18

SAP HANA: Customer history

1 Answers1