Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    3

    Unanswered: Designing "ITEMS" table for POS system

    hi , i am working on a point of sale (pos) system , and i have a table ( ITEMS )

    CREATE TABLE POS.ITEMS
    (
    ITEM_ID NUMBER,
    ITEM_NAME VARCHAR2(150 BYTE),
    MAX_UNIT NUMBER,
    MID_UNIT NUMBER,
    MIN_UNIT NUMBER,
    MAX_TO_MIN_FACTOR NUMBER,
    MID_TO_MIN_FACTOR NUMBER,
    MAX_UNIT_PRICE NUMBER,
    MID_UNIT_PRICE NUMBER,
    MIN_UNIT_PRICE NUMBER,

    MIN_QTY_FOR_DEMAND NUMBER,
    CATEGORY_ID NUMBER,
    PURCHASE_PRICE NUMBER,
    DISCOUNT NUMBER,
    RACK NUMBER,
    "ROW" NUMBER,
    SALES_TAX NUMBER,
    PURCHASE_UNIT NUMBER,
    SALE_UNIT NUMBER,
    BONUS NUMBER
    ) ;

    i see something very wrong with that structure which is all about " units " ( max_unit , mid_unit , min_unit , max_unit_price , mid_unit_price , mid_unit_price , max_to_min_factor , mid_to_min_factor ) columns

    each item could have unit or more , for example

    Cetal(medicine ) could be sold with these units ( packets , strips and bills ) ,

    another medicine could have only ( packets , and Ampoule ) and so on .

    so i think that i should have three tables ( ITEMS , UNITS AND ITEMS_UNITS ) ,

    ITEMS_UNITS table should hold units data ( max_unit , mid_unit , min_unit , max_unit_price , mid_unit_price , mid_unit_price )

    right ?

    if right then where should i put ( max_to_min_factor , mid_to_min_factor ) ?

    Please share me the right thing to do , and if there is anything you can add to my table , please do so .

    btw :
    (max_to_min_factor and mid_to_min_factor) are conversion factors .

    cetal units for example are

    packets = max_unit

    strips = mid_unit

    bills = min_unit )

    - if max_to_min_factor = 30 , that means that the packet has 30 bills .

    - if mid_to_min_factor = 10 , mans that the strip has 10 bills .

    and all that means that the packet has 3 strips ( 30/10) .

    thanks a lot
    Last edited by eslam elbyaly; 02-13-16 at 10:11.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You have a fourth table to hold the unit of measure conversion factors. This table holds the item id, the from and to units and the conversion factor relevant. You can either set it so that it also holds the reverse conversion for transactions going the wrong way, or code your UI to create/update reciprocal records when users add or maintain records.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •