item_id doesn't auto increase and is not primary key

[Rafe]

I found it when inserting some test data, item_id is not primary key and will not auto increase database is AWS instance.

[yaxing]

item_id is not PK. item_id & receipt_id is a composite key. I assumed item_id comes from merchant when passing us the receipt data since we don't have inventory.

[yaxing]

So now item_id needs to be set up when inserting a new item.

[Rafe]

ok, I got it, so item_id is more like sequence of item.

[yaxing]

no it's not sequence, I was thinking like the item_id is from the merchants inventory. For example, 'coke' is #12 in McDonald's, 'coke' also can be #1900 in KFC. Otherwise, if it item_id is not provided by the merchant, you can also regard it as a sequence id.

[Rafe]

another thing, total_cost is int, which can't calculate money amount correctly. It ignore the number smaller than 1

[yaxing]

sorry, it's an error in old version script, I changed it already

[Rafe]

I think it should be set as decimal

[yaxing]

I think since the precision is uncertain here, it might be better if we use double

[Rafe]

Floating point, like double, will ignore small precision when calculate. Because it is stored as number_exponent like 1.3231232_10^5, So it can handle very large and very small number, but when calculate, it might ignore small number at small digit.

http://stackoverflow.com/questions/1165761/decimal-vs-double-which-one-should-i-use-and-when

[yaxing]

I tried using double and there was no problem for this, I just tried decimal(10, 2) again and seems that there are sth wrong when calculating, prices all going wrong, maybe the problem of precision. Anyway, I can change both item_price & total price to decimal(10, 2) now. what do u think?

[yaxing]

I've changed tax, total_price in receipt table, item_discount, item_price in receipt_item into decimal(10, 2)

[Rafe]

hmm... tax should be able to keep smaller percentage, like 0.0875 what's the error when calculation?

2011/7/14 yaxing < reply@reply.github.com>

I've changed tax, total_price in receipt table, item_discount, item_price in receipt_item into decimal(10, 2)

Reply to this email directly or view it on GitHub:

https://github.com/gpryzby-wtt/Masxaro-Prototype/issues/18#issuecomment-1568448

[yaxing]

Sure, what about decimal(14, 6)?

[Rafe]

column receipt_item.id become receipt_item.receipt_id. makes program break. but receipt_id is correct name, keep it. What I mean before is that the primary key in it's own table should called id. But on other table, it should still called receipt_id.

[yaxing]

i forgot to push. I just pushed changes to master, try it

[Rafe]

Please check name in sql query name also, fix in feature/edit_and_update_receipt branch

[yaxing]

I modified them in branch backend and will merge into master before monday