November 10, 2008 at 6:14 am
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?
November 10, 2008 at 6:18 am
Do they have a reason for this decision?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2008 at 6:18 am
Are you trying to say you're looking for another job? Or that the company you work for is just positive by design?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
November 10, 2008 at 6:59 am
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 credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2008 at 9:15 am
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?
November 10, 2008 at 9:28 am
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?
Not all credits are negative. It depends if they are expenses, liabilities, or assests.
November 10, 2008 at 10:57 am
Lynn Pettis (11/10/2008)
Not all credits are negative. It depends if they are expenses, liabilities, or assests.
True, sorry, my Accounting knowledge is only so-so 🙂
November 10, 2008 at 6:06 pm
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:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2008 at 6:36 pm
This 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....:))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 10, 2008 at 10:56 pm
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
am i correct?
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 10, 2008 at 11:09 pm
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"
November 11, 2008 at 2:33 am
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?
November 11, 2008 at 2:36 am
November 11, 2008 at 5:03 am
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
Change is inevitable... Change for the better is not.
November 11, 2008 at 6:09 am
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.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply