November 11, 2008 at 7:25 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 11, 2008 at 7:35 am
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;):hehe::w00t:
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
November 11, 2008 at 10:26 am
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.
November 11, 2008 at 11:07 pm
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"
November 11, 2008 at 11:46 pm
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
Change is inevitable... Change for the better is not.
November 12, 2008 at 2:41 am
Chirag (11/11/2008)
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.
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.
November 12, 2008 at 7:20 am
Not much: the builtin scalar functions are generally very efficient per row, especially when compared to the cost of data retrieval per row.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 12, 2008 at 8:19 am
Jeff Moden (11/11/2008)
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.
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.
November 12, 2008 at 10:36 am
20 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.
November 13, 2008 at 6:12 am
As 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.
November 13, 2008 at 7:53 am
If 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.
Steve
November 13, 2008 at 6:07 pm
Lynn Pettis (11/12/2008)
Jeff Moden (11/11/2008)
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.
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.
Heh... The developers that were writing the interface were known to imbibe a wee bit too much. Thanks for checking, Lynn.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2010 at 10:48 am
CELKO (8/12/2010)
tymberwyld (11/10/2008)
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?They are insane. Time to update the old resume 🙂
You do realize that the last post in this thread is about 21 months ago, right?
August 12, 2010 at 10:59 am
Lynn Pettis (8/12/2010)
CELKO (8/12/2010)
tymberwyld (11/10/2008)
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?They are insane. Time to update the old resume 🙂
You do realize that the last post in this thread is about 21 months ago, right?
Still, it's a good advice. :w00t:
August 12, 2010 at 11:53 am
Ninja's_RGR'us (8/12/2010)
Lynn Pettis (8/12/2010)
CELKO (8/12/2010)
tymberwyld (11/10/2008)
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?They are insane. Time to update the old resume 🙂
You do realize that the last post in this thread is about 21 months ago, right?
Still, it's a good advice. :w00t:
Perhaps, but what good is good advice if delivered late?
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply