database design pos

[sanggano_boy]

---

isn't that expensive for a single transaction? especially if you want to modify selling price or inventory.

the design is correct, because this will help tract the correct price purchase of the item, in case you modify the price of the item master, this way the correct price for the purchase is kept.

[rbrallos]

butangi na ug PRICELIST bro or else dili na mag silbi imo POS


dili gyud pwede butangan ug price ang ITEM Master, Masterlist gani pure description ragyudna.

example) kung butangan nimo ug Price ang Item Master what if ma change ang price sa Item the next month? patay imo prev. sales ana diba?
kay ma usab man ug apil. so dapat naa gyudka PRICELIST, usbana.

[uchihacirxe]

butangi na ug PRICELIST bro or else dili na mag silbi imo POS


dili gyud pwede butangan ug price ang ITEM Master, Masterlist gani pure description ragyudna.

example) kung butangan nimo ug Price ang Item Master what if ma change ang price sa Item the next month? patay imo prev. sales ana diba?
kay ma usab man ug apil. so dapat naa gyudka PRICELIST, usbana.

I guess you have not understood well the design and relationship of the schema I presented. Check again, you'll have the answers to your concerns.

[rbrallos]

I guess you have not understood well the design and relationship of the schema I presented. Check again, you'll have the answers to your concerns.

are you sure? im not talking to you im talking to the TS, ang nakita nako sa database design palang daan sayop na you have to check it TS before you relate.

[Ricnew2]

its hard to desinged POS database i've been desingning my POS database for years and still finding out some mistakes about it. what rbrallos saying is you have no history in tracing the item price.
maybe it because the tables u presented is plane and simple just to have picture of it. but i think ur heading the right way is just that as time goes by u'll find still some modification needed to optimize ur database.

[uchihacirxe]

its hard to desinged POS database i've been desingning my POS database for years and still finding out some mistakes about it. what rbrallos saying is you have no history in tracing the item price.
maybe it because the tables u presented is plane and simple just to have picture of it. but i think ur heading the right way is just that as time goes by u'll find still some modification needed to optimize ur database.

If you keep it simple then you can achieve a better design.

"what rbrallos saying is you have no history in tracing the item price."

- You too have no idea how to work this based on the design I presented. The price history you are saying depends on what kind of "price history" you want. If you want to query the price changes ( updates ) made on a particular item then the design lacks a table. Because of the flexibility of the design I presented, I can add this table to solve that problem:

item_price_history
{
seqno (pk) autoincrement,
itemcode (fk),
barcode (fk),
update_dt,
update_by,
update_ws,
price_from,
price_to
}

This table logs price changes to the item master file via trigger in the latter.

But if you want a history of how an item performed at given time ( there are two solutions ):

Solution 1: Query the POS tables: Problem: Get the item performance from dec 1 2009 to dec 31 2009

select b.itemcode,
c.description,
total_qty_sold = sum(b.qty_sold),
b.price
from pos_hdr a,
pos_dtl b,
itemfile c
where c.itemcode = b.itemcode and
b.or_no = a.or_no and
b.pos_id = a.pos_id and
a.transaction_date_and_time >= '2009/12/01 00:00:00' and
a.transaction_date_and_time <= '2009/12/31 23:59:59'
group by b.itemcode,
c.description,
b.price

Solution 2: Query the sales table. Problem: Get the item performance from dec 1 2009 to dec 31

Select a.itemcode,
a. barcode,
b.description,
total_qty_sold = sum(a.qty_sold),
a.price
from sales a,
itemfile b
where a.itemcode = b.itemcode and
a.barcode = b.barcode and
a.sales_date >= '2009/12/01 00:00:00' and
a.sales_date <= '2009/12/31 23:59:59'
group by a.itemcode,
a.barcode,
b.description,
a.price

[uchihacirxe]

are you sure? im not talking to you im talking to the TS, ang nakita nako sa database design palang daan sayop na you have to check it TS before you relate.

Please present your design.

[Ricnew2]

If you keep it simple then you can achieve a better design.

"what rbrallos saying is you have no history in tracing the item price."

- You too have no idea how to work this based on the design I presented. The price history you are saying depends on what kind of "price history" you want. If you want to query the price changes ( updates ) made on a particular item then the design lacks a table. Because of the flexibility of the design I presented, I can add this table to solve that problem:

item_price_history
{
seqno (pk) autoincrement,
itemcode (fk),
barcode (fk),
update_dt,
update_by,
update_ws,
price_from,
price_to
}

This table logs price changes to the item master file via trigger in the latter.

But if you want a history of how an item performed at given time ( there are two solutions ):

Solution 1: Query the POS tables: Problem: Get the item performance from dec 1 2009 to dec 31 2009

select b.itemcode,
c.description,
total_qty_sold = sum(b.qty_sold),
b.price
from pos_hdr a,
pos_dtl b,
itemfile c
where c.itemcode = b.itemcode and
b.or_no = a.or_no and
b.pos_id = a.pos_id and
a.transaction_date_and_time >= '2009/12/01 00:00:00' and
a.transaction_date_and_time <= '2009/12/31 23:59:59'
group by b.itemcode,
c.description,
b.price

Solution 2: Query the sales table. Problem: Get the item performance from dec 1 2009 to dec 31

Select a.itemcode,
a. barcode,
b.description,
total_qty_sold = sum(a.qty_sold),
a.price
from sales a,
itemfile b
where a.itemcode = b.itemcode and
a.barcode = b.barcode and
a.sales_date >= '2009/12/01 00:00:00' and
a.sales_date <= '2009/12/31 23:59:59'
group by a.itemcode,
a.barcode,
b.description,
a.price

-- what i said is "maybe it because the tables u presented is plane and simple just to have picture of it"!! pls read. i said some table are not present maybe the poster made it just to have easy picture of it. its obvious that the layout presented lacks of tables and i'm not saying that you add it. what i'm saying is the layout is good is just that there are a lot of table should be considered. if you want to take this idea ur free to ignore this because i'm not forcing my idea!.

[Ricnew2]

i know ur schema is heading rigth way, im just sorry for myself that i have to share my opinion and tag that i dont get it. anyway it seems that u dont want any opinion.

[rbrallos]

i know ur schema is heading rigth way, im just sorry for myself that i have to share my opinion and tag that i dont get it. anyway it seems that u dont want any opinion.

tinood jud, ni hatag ta sa ato share makig dibate man nuon, mura jud siya ug korek, mao rani ako ma sulti, experienciado kaayoko anang POS infact naako 2 clients ni palit saako system hinimo nga POS, supplier sa manga dagko nga companies diri sa cebu. almost 4 years without problem ako design. to present a design i think dili na pwede i hatag lang ug basta basta ang design no, maybe gamay nga idea maka hatag, mao ako nakita sa design sa TS sayop jud, kinahanglan gyud ug sakto nga pag analysa.