InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 01:26:34 GMT20http://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxIt is an old thread but same goes here... OMG... Debit/Credit columns meaning one thing in Accounts Payable and meaning just the opposite in Accounts Receivable. And you had to make entries in both because of "double entry accounting". I don't know if they still do it but that's the way QuickBooks used to work behind the scenes, as well.Sun, 12 Sep 2010 21:31:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Ross McMicken (8/13/2010)[/b][hr][quote][b]Grant Fritchey (11/10/2008)[/b][hr]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.[/quote]This is an old thread, but I would point out that SAP stores accounting entries as positive numbers and uses another field to indicate whether the amount is a debit or credit. That's easy enough to deal with once you know, except that the indicators are H and S, the German abbreviations for the terms. Data entry in SAP is affected as well, as the user has to enter the amount, then enter a posting key that determines whether the amount is a debit or credit, as well as implying some other information. For a standard entry, debits are entered with a posting key of 40, credits with a posting key of 50. This was totally non-intuitive when I started using SAP after years of entering credits as negative numbers.[/quote]I never thought that thinking about accounting reports and queries would make me feel like a teen again, But all I can think is [b]OMG, OMG[/b]...Fri, 13 Aug 2010 08:45:02 GMTOld SQL Newbiehttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Grant Fritchey (11/10/2008)[/b][hr]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.[/quote]This is an old thread, but I would point out that SAP stores accounting entries as positive numbers and uses another field to indicate whether the amount is a debit or credit. That's easy enough to deal with once you know, except that the indicators are H and S, the German abbreviations for the terms. Data entry in SAP is affected as well, as the user has to enter the amount, then enter a posting key that determines whether the amount is a debit or credit, as well as implying some other information. For a standard entry, debits are entered with a posting key of 40, credits with a posting key of 50. This was totally non-intuitive when I started using SAP after years of entering credits as negative numbers.Fri, 13 Aug 2010 08:31:01 GMTRoss McMickenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]Ninja's_RGR'us (8/12/2010)[/b][hr][quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]CELKO (8/12/2010)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)[/quote]You do realize that the last post in this thread is about 21 months ago, right?[/quote]Still, it's a good advice. :w00t:[/quote]Perhaps, but what good is good advice if delivered late?[/quote]I use google all the time for sql stuff (after I can't find what I need here) and yet every so often I get back to a thread here.Just today I was stuck in ssrs and I found a 2-3 years old thread here that got me unstuck. So I can only imagine how many people are finding that thread even years later which could use that advice.Anyhow, the thread age is usually the last thing on my mind when I use google. I only care about getting my stuff done and finding the answers I need.And a good laugh is always a great bonus.Thu, 12 Aug 2010 17:09:03 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]tymberwyld (8/12/2010)[/b][hr]Well, not entirely useless, I got a good laugh as I thought about this 'ole job. Moved on to bigger and better things...so the advice was taken :-P[/quote]My apologies then. As long as you got a laugh then it is useful:-DThu, 12 Aug 2010 12:53:35 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxWell, not entirely useless, I got a good laugh as I thought about this 'ole job. Moved on to bigger and better things...so the advice was taken :-PThu, 12 Aug 2010 12:48:32 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]Ninja's_RGR'us (8/12/2010)[/b][hr][quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]CELKO (8/12/2010)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)[/quote]You do realize that the last post in this thread is about 21 months ago, right?[/quote]Still, it's a good advice. :w00t:[/quote]Perhaps, but what good is good advice if delivered late?[/quote]Good advice is only good when delivered in a timely fashion. When it comes too late, then it is only useful to somebody else who may perchance the thread. It is useless thoughts to the person at whom the advice is directed at this point.Thu, 12 Aug 2010 12:31:33 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Ninja's_RGR'us (8/12/2010)[/b][hr][quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]CELKO (8/12/2010)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)[/quote]You do realize that the last post in this thread is about 21 months ago, right?[/quote]Still, it's a good advice. :w00t:[/quote]Perhaps, but what good is good advice if delivered late?Thu, 12 Aug 2010 11:53:32 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (8/12/2010)[/b][hr][quote][b]CELKO (8/12/2010)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)[/quote]You do realize that the last post in this thread is about 21 months ago, right?[/quote]Still, it's a good advice. :w00t:Thu, 12 Aug 2010 10:59:04 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]CELKO (8/12/2010)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)[/quote]You do realize that the last post in this thread is about 21 months ago, right?Thu, 12 Aug 2010 10:48:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]tymberwyld (11/10/2008)[/b][hr]Along with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?[/quote]They are insane. Time to update the old resume :-)Thu, 12 Aug 2010 10:15:37 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (11/12/2008)[/b][hr][quote][b]Jeff Moden (11/11/2008)[/b][hr][quote][b]Lynn Pettis (11/11/2008)[/b][hr][quote][b]Jeff Moden (11/11/2008)[/b][hr]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.[/quote]I'll have to look into this. We use PeopleSoft where I work.[/quote]Perfect... I wasn't involved that much in what they were doing and it would be good to know for sure. Thanks, Lynn.[/quote]Talked with our PeopleSoft Administrator, and it isn't PeopleSoft. Values stored only as positive values are always positive. Accounting values are stored as appropriate positive or negative values.[/quote]Heh... The developers that were writing the interface were known to imbibe a wee bit too much. Thanks for checking, Lynn.Thu, 13 Nov 2008 18:06:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxIf this is trully an accounting system, what do you do with adjusting entries? You always have entries that are posted that will reverse or adjust the original amounts and this would naturally have a reverse sign.SteveThu, 13 Nov 2008 07:53:16 GMTsteve blockhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxAs I think I learned long ago, accounting/bookkeeping procedures predate the recognition of negative numbers. But your management seems ahead of the curve, why don't you ask them to define some nice round number to use instead of pi.Thu, 13 Nov 2008 06:12:45 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx20 Years ago I was working with an old flat file accounting system, and they stored the value in one field, and the sign in another. It is not necessary however. I've worked with a lot of accounting systems since then, and none of them did that.Wed, 12 Nov 2008 10:36:44 GMTOld SQL Newbiehttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Jeff Moden (11/11/2008)[/b][hr][quote][b]Lynn Pettis (11/11/2008)[/b][hr][quote][b]Jeff Moden (11/11/2008)[/b][hr]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.[/quote]I'll have to look into this. We use PeopleSoft where I work.[/quote]Perfect... I wasn't involved that much in what they were doing and it would be good to know for sure. Thanks, Lynn.[/quote]Talked with our PeopleSoft Administrator, and it isn't PeopleSoft. Values stored only as positive values are always positive. Accounting values are stored as appropriate positive or negative values.Wed, 12 Nov 2008 08:19:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxNot much: the builtin scalar functions are generally very efficient per row, especially when compared to the cost of data retrieval per row.Wed, 12 Nov 2008 07:20:15 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Chirag (11/11/2008)[/b][hr][quote][b]ta.bu.shi.da.yu (11/11/2008)[/b][hr][quote][b]Chirag (11/10/2008)[/b][hr]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.[/quote]Wouldn't you just apply an ABS function on the value?[/quote]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.[/quote]Actually, I've just given this a bit of thought. You'll have to excuse me, I'm a bit of a newbie when it comes to performance issues - but wouldn't applying an ABS function to a column in the SELECT clause cause a bit of a negative performance hit? Curious.Wed, 12 Nov 2008 02:41:34 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (11/11/2008)[/b][hr][quote][b]Jeff Moden (11/11/2008)[/b][hr]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.[/quote]I'll have to look into this. We use PeopleSoft where I work.[/quote]Perfect... I wasn't involved that much in what they were doing and it would be good to know for sure. Thanks, Lynn.Tue, 11 Nov 2008 23:46:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]ta.bu.shi.da.yu (11/11/2008)[/b][hr][quote][b]Chirag (11/10/2008)[/b][hr]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.[/quote]Wouldn't you just apply an ABS function on the value?[/quote]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.Tue, 11 Nov 2008 23:06:59 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Jeff Moden (11/11/2008)[/b][hr]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.[/quote]I'll have to look into this. We use PeopleSoft where I work.Tue, 11 Nov 2008 10:26:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Jeff Moden (11/10/2008)[/b][hr]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;):hehe::w00t:[/quote]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. Tue, 11 Nov 2008 07:35:13 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]krayknot (11/10/2008)[/b][hr][quote][b]tymberwyld (11/10/2008)[/b][hr]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?[/quote]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 change[/quote]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.Tue, 11 Nov 2008 07:25:41 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxThanks 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.Tue, 11 Nov 2008 06:09:12 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxOh 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.Tue, 11 Nov 2008 05:03:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Chirag (11/10/2008)[/b][hr]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.[/quote]Wouldn't you just apply an ABS function on the value?Tue, 11 Nov 2008 02:36:23 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxEvidently 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 [i]was[/i] the numbskull who came up with that design? Were they a DBA - or even someone with any programming experience at all?Tue, 11 Nov 2008 02:33:40 GMTta.bu.shi.da.yuhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Grant Fritchey (11/10/2008)[/b][hr]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.[/quote]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.Mon, 10 Nov 2008 23:09:10 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]tymberwyld (11/10/2008)[/b][hr]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?[/quote]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 changeam i correct?Mon, 10 Nov 2008 22:56:32 GMTkrayknothttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxThis would bring out the old Bastard DBA from Hell in me. Just encrypt all of the views and all outputs from the DB, calling it a new SOX measure, then mention that they must talk to SEcurity to get their own personal security code to decrypt the output.....Or -Comply with the request, and store every number in Hex form, again refusing to convert it back to decimal for output purposes.....(it's been one of them days, so probably best not to heed any of this advice....:))Mon, 10 Nov 2008 18:36:11 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxHeh... 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;):hehe::w00t:Mon, 10 Nov 2008 18:06:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]Lynn Pettis (11/10/2008)[/b]Not all credits are negative. It depends if they are expenses, liabilities, or assests.[/quote]True, sorry, my Accounting knowledge is only so-so :)Mon, 10 Nov 2008 10:57:26 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspx[quote][b]tymberwyld (11/10/2008)[/b][hr]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?[/quote]Not all credits are negative. It depends if they are expenses, liabilities, or assests.Mon, 10 Nov 2008 09:28:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxBasically 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?Mon, 10 Nov 2008 09:15:14 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxNot 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.Mon, 10 Nov 2008 06:59:24 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxAre you trying to say you're looking for another job? Or that the company you work for is just positive by design?Mon, 10 Nov 2008 06:18:09 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxDo they have a reason for this decision?Mon, 10 Nov 2008 06:18:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic599801-373-1.aspxAlong with a lot of other horrible decisions, I am now not allowed to use Negative numbers in any database design. Instead, I need to add another column (ex. Operator) which tells whomever that the data in the amount column is either Positive or Negative. Huh? Is there something I missed during my last 10 years designing databases?Mon, 10 Nov 2008 06:14:21 GMTtymberwyld