SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storing Negative Numbers


Storing Negative Numbers

Author
Message
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 1865
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"
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 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
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88546 Visits: 41130
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 275
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.



RBarryYoung
RBarryYoung
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15226 Visits: 9518
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 example
user entered 100 and selects '-'
the output would be: 100 * -1 = -100
or 100 * 1 =100 or no change


Actually, 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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2342 Visits: 19324
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"... BigGrinWinkHehew00t


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 question
How to post performance problems
Tally 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."
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40450 Visits: 38567
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 1865
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"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88546 Visits: 41130
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search