Jeff Moden wrote:
Rick, please tell me that you're not advocating a "panacea length" for VARCHAR(N) where "N" is something like 256. Please tell me you're not advocating the NUMERIC(18,N) should be a "panacea" for numeric values.
Well not quite that extreme. But let me see, when I was in grade school, our phone number was '26R11' (no area code, no exchange, that just meant line 26 with one long ring and one short ring on the hand-crank wall phone, which everyone on line 26 heard and could pick up. You told the switchboard operator you wanted to connect to Mansfield, Illinois, number 26R11. And our address was 'Rural Route 3, Mansfield, Illinois. ( no street number , no zip code, not even the old 5-digit version. And essentially all city street numbers were three digits, maybe four. Now around us essentially all street numbers are at least 5 digits...but not ALL of them. There are still some in the central parts of town that are still 3 digits. Yours and my SS numbers, I believe are all officially NNN-NN-NNNN, but who is to say for how long? And should you store it with or without the dashes? And with the population growth (especially with the open borders), how long is it until somebody decides to make one of those 'standard' sized segments two digits longer to cover the growth. You can fit my bank account balance into a 5,3 format, but thank God not my IRA balance.
What I'm advocating is responsible, common-sense analysis and design considering that the extra data storage space, processing considerations, etc are very, very cheap when compared to system redesign every few years. Or...you be the one to go tell the executive board that you have yet another system redesign that has to be done by 1-1-2023, will cost $400k, and will delay three other projects
In other words, it's far better to VALIDATE that a string of undetermined storage size in fact is 11 bytes long, does contain exactly (for now) 9 numeric digits in a 3-2-4 pattern delimited by exactly two (for now) hyphens, not commas or decimal points or slashes, but hyphens. Note that I can quickly alter the specs by retyping the previous description. But I can still throw it in that horrible, ugly VARCHAR data element.
Or we can save several terabytes by making the amount field for checks max at 99,999.99. But then Nancy Pelozi (actually her husband) sells $200,000,000.00 in stock and brings in the check for deposit. Or all of a sudden we have to store bitcoin amounts that exceed the capacity of small int.
Even a simple Yes/No value may someday need to include an option for MAYBE.
Just because you CAN do something doesn't mean you SHOULD do it. I'm advocating that we, as IT professionals, use our common sense to avoid putting our employers in very expensive boxes and ourselves up against very uncomfortable demands.
Disaster Recovery = Backup ( Backup ( Your Backup ) )