Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Storing Negative Numbers Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, November 10, 2008 11:09 PM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 25, 2016 1:11 PM Points: 2,365, Visits: 1,865
 Grant Fritchey (11/10/2008)Not unless I've missed it to. If the value of a number is -3, it's -3, not 3 with some negative sign kept elsewhere that may or may not change it. That's a bit of a freaky design. Although, I'd have to check with an accounting friend for the details, I think there is a method of accounting whereby you designate everything as a value and then describe the value as either a credit or debit, + or -, maybe that's what you're dealing with.The technique that you are talking about is quite widely used in accounting applications. I remember doing the same thing myself. You take the sum of all debits & multiply it with -1 , take sum of all credits and then just the sum the total of debits and credits to get the current amount. I think this is quite efficient too. But i beleive this can be applied only to some scenarios accounting being one. Otherwise a negative values needs to stored as negative.You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report. "Keep Trying"
Post #600410
 Posted Tuesday, November 11, 2008 2:33 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, July 3, 2011 7:09 AM Points: 233, Visits: 494
 Evidently never heard of twos compliment. As Lynn Pettis wrote:"Not all credits are negative. It depends if they are expenses, liabilities, or assests."Thus it makes no sense not to allow negative values. Thus even splitting into debits and credits you'll need a sign column. The problems with the design as I see it will be when you want to sum, average, etc. the values. The only way I can see you'll be able to do it with the design constraint you've been given is to setup a cursor to loop through each value and if the sign value is positive leave the value alone, and if the sign value is negative then you'll need to times the amount by -1. Seems like a massive performance hit for no good reason, and one that you'll eventually probably have to revert anyway. Who was the numbskull who came up with that design? Were they a DBA - or even someone with any programming experience at all? Random Technical Stuff
Post #600499
 Posted Tuesday, November 11, 2008 2:36 AM
 SSC Veteran Group: General Forum Members Last Login: Sunday, July 3, 2011 7:09 AM Points: 233, Visits: 494
 Chirag (11/10/2008)You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report.Wouldn't you just apply an ABS function on the value? Random Technical Stuff
Post #600500
 Posted Tuesday, November 11, 2008 5:03 AM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #600570
 Posted Tuesday, November 11, 2008 6:09 AM
 SSC Veteran Group: General Forum Members Last Login: Tuesday, September 29, 2015 9:35 AM Points: 283, Visits: 271
 Thanks for the comments (love it Jeff). I agree that accounting would be a different scenario, heck, maybe even storing an OrderAdjustments table where you have an AdjustmentType (SalesTaxState, SalesTaxFederal, Shipping, Packaging, Coupon Discounts, etc.) where all amounts could be stored in positive form because the "Type" tells you what the amount is (+ or -) (even though I would personally store those as +/- values).However, in the situation I am in, I do agree that the UI should make it easy on the user, but store it in one column as positive or negative amount. However, I've already lost the battle because "We don't know what future requirements will be...", really? So it's fun to just hear all the other lame requirements out there.
Post #600600
 Posted Tuesday, November 11, 2008 7:25 AM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 krayknot (11/10/2008)tymberwyld (11/10/2008)Basically what's going on here is that someone is looking at some wireframes of the UI and that's getting translated directly into a database design. If they want to give users an easy way to enter Postive / Negative amounts, by all means, give them a stupid designator / operator selector. However, that should not be how it's stored in the database. As far as accounting goes, I understand your point, but I would think that all Credits would be stored as Negative amounts, yes?whatever the value and the operator the user selects. you multiply that value by -1. for exampleuser entered 100 and selects '-'the output would be: 100 * -1 = -100or 100 * 1 =100 or no changeActually, for a wireframe UI, the "operators" could easily be 2D (or 3D) unit vectors. In which case, it may be perfectly correct to say that the "magnitude" field should never be negative, as that could mess up a lot of rendering software. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #600657
 Posted Tuesday, November 11, 2008 7:35 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, January 30, 2015 8:42 AM Points: 1,332, Visits: 19,324
 Jeff Moden (11/10/2008)Heh... if you really want to throw a ball of wax at them before you decide to find a place with reasonable requirements, tell them the column should be named "IsNotNegative"... :D;)Lol - that's just mean. Reminds me of the one time they let me lead "Simon Says" in grade school - Simon says hop on one foot.Don't stop. ---------------------------------------------------------How best to post your questionHow to post performance problemsTally Table:What it is and how it replaces a loop"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #600670
 Posted Tuesday, November 11, 2008 10:26 AM
 SSC-Insane Group: General Forum Members Last Login: Friday, December 2, 2016 4:50 PM Points: 23,515, Visits: 37,731
 Jeff Moden (11/11/2008)Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this.I'll have to look into this. We use PeopleSoft where I work.
Post #600808
 Posted Tuesday, November 11, 2008 11:06 PM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 25, 2016 1:11 PM Points: 2,365, Visits: 1,865
 ta.bu.shi.da.yu (11/11/2008)Chirag (11/10/2008)You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report.Wouldn't you just apply an ABS function on the value?Agreed. Lets say we are storing credit amounts as negative values. In many places we need to show these as positive values. So in all such cases we would have to use ABS function. So if we were storing credit amounts as postive value we need not do this.Iam not defending either design scenarios but like everything the correct answer is "it depends". Depends on the type of queries that are going to hit the db, the frequency these queries, the amount of data and the overall db dbdesign. "Keep Trying"
Post #601139
 Posted Tuesday, November 11, 2008 11:46 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 7:19 PM Points: 42,047, Visits: 39,434
 Lynn Pettis (11/11/2008)Jeff Moden (11/11/2008)Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this.I'll have to look into this. We use PeopleSoft where I work.Perfect... I wasn't involved that much in what they were doing and it would be good to know for sure. Thanks, Lynn. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #601151

 Permissions