Inventory API

Table of Contents

• Introduction
• Data Model
• API
• Database Tips

Introduction

This is the API that manages all products for Dan’s cafe. Products are broken down into 2 main categories:

• Drinks

• Snacks

This API allows Dan to determine what products are available, how much they cost and how many of the items he has left available to sell.

Prices for the products can change based on demand, events and the season.

Data Model

• products

• category_labels

• prices

• specials

API

The API can be viewed here: [API link to file]

Database Tips

Prices have their own table product_prices which overload a price if there is some special on for the product.

The qay the prices are selected is by using the seqnum approach:

Listing 1. Select the latest effective date

select p.*
from (
    select p.*,
           row_number()
    over (
        partition by product_id
        order by effective_date desc)
    as seqnum
    from product_prices p
) p where seqnum = 1;

This will ultimately order everything by the effective_date in the sub query and assign a seqnum value (1..count). The where clause on the outer query then selects just hte first value out of the list.

For the full query to get products and their latest price:

Listing 2. Get products and prices view

create view product_latest_prices as
select a.product_id as product_id,
       a.display_name as display_name,
       a.description as description,
       a.type as type,
       a.sku as sku,
       aa.price_id as price_id,
       aa.price as price,
       aa.effective_date as effective_date
from product a
         join product_prices aa on a.product_id = aa.product_id
where aa.price_id in (
    select p.price_id
    from (select p.*, row_number() over (partition by product_id order by effective_date desc) as seqnum from product_prices p) p
    where seqnum = 1
);