I recently had the opportunity to refactor and normalize an extremely large database in MySQL. I love refactoring as a matter of course, and normalization just makes me all a-tingle, so it’s good work if I can get it.

I was noticing as I pored through the fields in the various tables, that many, many fields had the same varchar length. 90% of them were varchar(100), including zip codes. Well, that’s when the zip codes weren’t integers, but I digress.

The point is this: I did some checking online, and I couldn’t find a good guide to field sizes, so I thought I’d just share a little of an approach I feel is common-sensical and worthy of at least writing down.

Let’s take a look at a record, and go from there:

John Smith, 123 Somewhere Street Northwest, AmityMothertonvilePokeno, New Hampshire, 03101, 603-555-1212, jsmith2007@funky-nowhere.com

That’s pretty basically a bit of contact information. It’s normal, it’s average, and we all use it constantly in just about every app we develop using MySQL and PHP. So how long should we make the fields?

The name. “John Smith” is only ten characters, but what if someone has a longer name? What about “Luxaranimous Fitzgerald Luntensteinivilosky”? I think we could all agree that’s an egregiously long name, and it still comes in at 43 characters. So maybe we bump it up for a bit of a buffer, and call it 55 characters. Now I feel better, and I have my new “Fullname” field size for all time.

Next we have the street address. I once knew a person who lived on Martin Luther King Boulevard, and that’s decent size, so I’ll use that as a baseline. Assuming 6 characters for the number, and 5 for “north” or “south”, we come in at 39 characters. Add an apartment (Apartment 123) and we’re at 41 characters. Again, we pad it, and I come up with 55 as a decent field size for an address.

City and state are both easy. The longest city name in English is 60 characters long (look it up if you don’t believe me), and it is also the longest ‘place’ name in the English language. So that means that the city field is 60, and the state field, if you’re not just using abbreviations, is 60 as well. (Longest “place”, remember?)

Zip codes/postal codes in the US are easy, that’s nine without the dash, ten with the dash (12345-1234 or 123412345). Foreign postal codes are a bit longer in some cases, and according to This site, the longest postal code in the world is currently 10 characters long.

Next, let’s take a look at the dreaded phone number. I know this one always gave me fits when I started out, never could decide what to do with it. Do I store it into separate fields, do I exclude foreign phone numbers? If I don’t exclude foreign phone numbers, how do I make it familiar for all?

Relax, first of all. The longest phone number in existence, country code and all, is 14 characters by all accounts. The format varies wildly from country to country, so if you’re dealing with international phone numbers, you can still split them up the way you would with US numbers, just make sure you add a country code field. As long as the split fields end up adding to 14, you’re ok. If you want to add in the ability for separators, dashes, dots, morse code, whatever, jack it up to twenty. No need to go overboard. This methodology of course applies to mobile phone numbers and fax numbers, as well.

And then the email address field. My email address is “anthony.levensalor@opentravelsoftware.com“. That’s 41 characters right off the bat, and I know I don’t have the longest. In fact, the longest email address can be 64 + 1 + 255 characters long (user name, @ sign, domain name) according to the IETF.

Does that mean your email field needs to be 320 characters long? Well, technically, it is best practice to make sure you cover the extremes, so yeah. Just make sure you crunch that by allowing the field to be null. If that sounds silly to you, rest assured it sounds silly to me too. But somebody’s gonna have a 320 character long email address, and you know it.

So here’s the basic field specs, as much for my edification as yours:

full name: varchar(55)
address:  varchar(55)
city:         varchar(60)
state:       varchar(60) [or varchar(2) if you're using the US abbreviations only]
zip:          varchar(10)
phone:     varchar(14) [that's just digits, no symbols. Of course, in the US, you only need 11]
email:       varchar(320)

Until next time, I hope this helps you out.

~A!

AS much as Steve Jobs and the Apple community want to complain about Microsoft “stealing” features (wasn’t there already a lawsuit about that in the 80’s, and MS won?), one of the things I liked best about Vista in the tests I did on it was the destop search.

I know, it seems unnecessary somehow, what with google desktop and all the other possibilities out there for indexing and searching the computer, but I love the fact that it is integrated directly with the operating system and just makes searching a hell of a lot easier. And faster. And more comprehensive.

I was trolling around the Microsoft site’s XP downloads and came across a version of the search that runs on XP. Since I’m not really ready to upgrade to Vista (bought a copy of the upgrade, waiting until I get better video), I went ahead an installed it. (You can download it here).

Once installed, it creates a little search bar in the upper-right hand corner of the screen. Start typing and it brings up a list of the most relevent matches right away. Click “more”, and you get a full interface that allows you to choose different types of files, specify search criteria, just a whole bunch of cool stuff.

The way I look at it is this: I’m tempted to try out an iBook, because the hype is hard to resist, and my wife loves hers. But let’s be pragmatic for a moment. I am a professional software developer, and I do a lot of heavy lifting on my machine. I write PHP, Perl, MySQL, HTML, CSS, Javascript, Java, VB.NET, VB6, C++, and once in a while a little Delphi to pass the time. Do I really want to risk all those working on Mac (with or without the emulation of windows they provide now)?

Not really, no. Besides, I like using windows. I also like using Linux. I’d probably like using OS X, too. But the value of understanding the software that’s running your machine cannot be overstated. Perhaps I’m just with Windows because I’ve been on Microsoft products for all 22 years of my programming life, and I’ve never had something happen I couldn’t fix. Even when I was eight, DOS was easier to work with than The Apple IIe we had at the grammar school.

This isn’t a “Windows is good, everything else is bad” article, just saying that’s a nifty search they put in, and I’m glad they ported it down to XP.

~A!

 

Q: When I modify php.ini file in “c:\php\–IIS 6.0 is not picking it up.  Any changes made in php.ini do not reflect when I run phpinfo().  However, I can run *.php files.  I setup IIS 6.0 with the appropriate web extensions–on the web service extension and the web site directory mapping.  Both pointing to c:\php\php5isapi.dll  Just can’t get php.ini to pick up???
Thank you for your help and God Bless!
- Tony

A:

You are slightly outside my strongest area when engaging in IIS interop with PHP, but I will say that the little experience I have with it inclines me to think that your php.ini file should be in a system path for windows, e.g. c:\windows. I’ve had a lot of luck getting things to pick up appropriately when I copied the php.ini file into system directories and restarted the IIS process.

~A!