April 29, 2011 at 2:27 pm
UMG Developer (4/29/2011)
Duh, I was missing the obvious issue by paying more attention to the MAX portion. You are right, there would be no point in declaring it as more than VARCHAR(2816) if you only want to store one phone number of each type. But there is no reason I couldn't have multiple home, cell, fax, etc. numbers listed as long as they don't need to be identified separately. (The position in the string could identify the order to call them.)
But there is a really good reason for using MAX: it absolutely prevents this denormalised nightmare being used as a key column in an index! 😀
And of course it contributes to the general zaniness of the thing (and I was trying to out-zany Peter, because I was very jealous of his lovely piece of zanity) 😛
Tom
April 29, 2011 at 6:26 pm
Tom.Thomson (4/29/2011)
UMG Developer (4/29/2011)
Duh, I was missing the obvious issue by paying more attention to the MAX portion. You are right, there would be no point in declaring it as more than VARCHAR(2816) if you only want to store one phone number of each type. But there is no reason I couldn't have multiple home, cell, fax, etc. numbers listed as long as they don't need to be identified separately. (The position in the string could identify the order to call them.)But there is a really good reason for using MAX: it absolutely prevents this denormalised nightmare being used as a key column in an index! 😀
And of course it contributes to the general zaniness of the thing (and I was trying to out-zany Peter, because I was very jealous of his lovely piece of zanity) 😛
As all the voice geeks will tell you, it may be necessary to store an entire e164 formatted phone number, especially for an international company, which requires FIFTEEN digits of space per number including any required special characrters like the + sign.
This gives you about 143,165,576 numbers you can store in a 2GB column if you are using the order to signify the type of phone number. I hope that's still enough... me, I only need about 4.
But hey!! Something else to think about. I use Microsoft Lync now so I almost never use phone numbers anymore. I use Active Directory to search and call by name. So how soon until the world stops using phone numbers and just starts using names like DNS??? "Just call our office at phone@aos5.com". How much easier, right??
Lync makes calls using SIP which uses names so my work phone number is now "sip:peter.trast@aos5.com" for SIP capable devices, usually it looks just like your email address if the admin decides to go that route. Now it is not quite as simple as that because your company cannot call my "number" directly unless we are "federated". But if you are on MSN messenger, for example, you could, because by default Lync is federated with MSN and some other public VOIP/IM.
Point being we probably won't be storing numbers like that anymore eventually. Your "number" will simply be your name concatenated by some dialing rule to a domain... making this conversation pointless!
Lync rocks!!! 😀 (but SQL is still my first love)
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 4, 2011 at 7:32 am
My experience has been that when many developers and DBAs say denormalized, they actually mean unnormalized. You can't reverse what you never did in the first place... :rolleyes:
Atomicity is always relative to the business need. When I worked in telephony, the numbers you're used to were not atomic data for us. We stored them in their constituent pieces for our uses. Similarly many states' driver license numbers contain a myriad of codes that may need to be analyzed individually.
May 4, 2011 at 12:49 pm
My experience has been that when many developers and DBAs say denormalized, they actually mean unnormalized. You can't reverse what you never did in the first place...
Completely agree
Atomicity is always relative to the business need. When I worked in telephony, the numbers you're used to were not atomic data for us. We stored them in their constituent pieces for our uses. Similarly many states' driver license numbers contain a myriad of codes that may need to be analyzed individually.
An excellent point. I have a database where we store the telephone numbers in their component parts. And while normally the street number and street name are stored as one, one time I needed to break them out.
Viewing 4 posts - 46 through 48 (of 48 total)
You must be logged in to reply to this topic. Login to reply