02-13-16, 09:18 #1Registered User
- Join Date
- Jul 2014
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_NAME VARCHAR2(150 BYTE),
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 )
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 .
(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.
02-26-16, 09:40 #2Registered User
Provided Answers: 2
- Join Date
- Sep 2006
- Surrey, UK
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.