web-design – Need suggestion on Inventory System database …

Lets start by pricing. It is always a good practice to have multiple pricelists to be free to choose the pricing scheme for each individual customer or POS. So we create the pricelist table: /*Price list definition *pr1_kind: S if this pricelist is used in Wholesale Sales R is this […]

Lets start by pricing. It is always a good practice to have multiple pricelists to be free to choose the pricing scheme for each individual customer or POS. So we create the pricelist table:

/*Price list definition
 *pr1_kind: S if this pricelist is used in Wholesale Sales 
            R is this pricelist is used in Retail Sales
            P if refers to products purchased
            C if it refers to product Cost. Cost prices are updated
              by a costing procedure. Not in the scope of this thread.
*/
create table pricelist1
(
  pr1_code      number(10) not null
  pr1_name      varchar2(30) not null,
  pr1_kind      varchar2(1) not null, 
  pr1_currency  varchar2(3) not null,
  primary key(pr1_code)
);

On each pricelist we should have the prices per item. In order to have price history we add a date field that keeps the date the price starts to be effective. A new record for the same product at another date will change the price. For example product 1 has the price of 10$ up to 13/6/2010. After that date the price should be 12$ (nothing is getting cheaper these days…). To handle this case we should add 2 records:
1/1/2000, product 1, 10$ (a very old date)
13/6/2010, product 1, 12$
Now the prices table:

/* Pricelist Prices
 * pr2_date: The date the price is effective.
 */
create table pricelist2
(
  pr2_code      number(10) not null references pricelist1(pr1_code),
  pr2_date      date       not null
  pr2_itemno    number(10) not null,
  pr2_price     number(10,2) not null,
  primary key(pr2_code, pr2_date, pr2_itemno)
);

Because there are many prices per product, when looking for THE price, we should first know the pricelist.
This can be found in many ways depending on our needs. As an example when selling to a customer we can define
at the customer table the preferred pricelist to use:

create table customer
(
  cus_code number(10) not null,
  ......
  cus_prlist number(10) not null references pricelist1(pr1_code)
  .....
);

If on the other hand we have a retail store we can define the preferred pricelist at the store level.

In order to simplify code, we will create a PL/SQL (I only use Oracle….) package to handle prices:

create or replace package prices_pkg as

function GetPrice(pPrList in pricelist1.pr1_code%type, 
                  pDate   in date,
                  pItemNo in pricelist2.pr2_itemno%type
                 ) return pricelist2.pr2_price%type;

end;

create or replace package body prices_pkg as

function GetPrice(pPrList in pricelist1.pr1_code%type, 
                  pDate   in date,
                  pItemNo in pricelist2.pr2_itemno%type
                 ) return pricelist2.pr2_price%type is
  cursor c1 is
  select pr2_price
    from pricelist2, pricelist1
   where pr2_code   = pPrList
     and pr2_date  <= pDate
     and pr2_itemno = pItemNo
     and pr1_code   = pr2_code
   order by pr2_date desc;
  f1 c1%rowtype;
begin
  --Fetch only one value. This will fetch the last record before pDate date.
  open  c1;
  fetch c1 into f1;
  close c1;
  return f1.pr2_price;
end;

end;

The SQL to get the current prices for some products will be:

select prd_itemno, prd_descr, pricelist_pkg.GetPrice(1, sysdate, prd_itemno) as price
  from products
 where .....

So simple. Another bonus of this scheme:We can anytime change the GetPrice function to handle more complex situations. For example if we need the price always in local currency, we can add a Currency->LCY transforamtion in the function without changing a single line of code in the application.

I will answer the other parts in another post.

Next Post

Charles E. Mather 3d, insurance executive and descendant of old Quaker family, dies at 86

Charles E. Mather 3d, 86, of East Falls, a fourth-generation insurance executive, sportsman, and cultural leader in Philadelphia, died Monday, Sept. 21, of a pulmonary embolism at Einstein Medical Center Philadelphia. Known as ‘Peter,’ Mr. Mather was the son of Buvel Folwell and Charles E. Mather II. He was […]