Is it bad to Alter a Table with Data in it?

Is it a big deal if you ALTER a Table/Columns after they have been created and contain data?

I am designing my database and trying to make good design decisions, but the reality is that “You just don’t know until you know!!”

So, for instance, let’s say I think a VARCHAR(20) was a good idea but it turns out I need a VARCHAR(50).

Or maybe I thought DECIMAL(5,2) was sufficient until I realized I needed a bigger range like DECIMAL(8,2).

Or maybe I have to make an even bigger change like going from INT to TEXT?

Or maybe I chose ENUM only to decide it is too inflexible and so I want to create a “Look-up Table” instead?!

Is this that big of a deal to have to “re-design” your Tables/Columns somewhat?

TomTees

Anything between 0 and 255 characters in a varchar uses one byte for the length field plus one byte per character and so there is no saving if you reduce the maximum length to 127 as all that means is that one bit will always be zero.

If you go over 255 bytes then you would need to use either a text field of some sort (if the version of the database you are using supports it) or a blob to hold the data.

So there is no performance or design hit by specifying a VARCHAR(255) even if users only maybe use 50-100 characters?

(I mean I understand that VARCHAR is v-a-r-i-a-b-l-e, however nothing is free!)

Also, what would you do if you needed more than 255 characters?

What would be the next logical step as far as data-type in that case?

TomTees

With varchars it doesn’t hurt to specify 255 as the size if you are are unsure since the actual space used will be dependent on the data and there will be no saving whatever in changing it to a smaller number later. The only time it makes sense to enter a smaller number is where you know for certain what the maximum length is.

Having specified varchar(255) for fields where you don’t know the maximum that may apply the only time you’d ever need to change it is if it turns out that 255 is too small.

So it sounds like I should go VARCHAR(255) for anything I am uncertain about in length - but know will be under 255 characters?

Most fields I will try to utilize look-up tables to force users to make a selection from a pre-determined list, but for fields like “First Name”, “Last Name”, “Address”, etc this seems like a good idea.

TomTees

But my whole point is sometimes you just don’t know until after things go live, so it would seem it is better to over-estimate and then down-size later, versus under-estimating and losing data.

I mean you wouldn’t want a BLOB data-type if the field width could range from 5 to 50, but as a for instance, it seems like good design to err on the side of making fields slightly larger than need be and then scaling back if the data shows you over estimated.

To me, it is pretty hard to guess how big to make “First Name”, “Last Name”, “Address1”, or “City” fields because - at least in America - the days of “Amy Williams”, “Bob Miller”, “Patty Smith” are long gone…

TomTees

So it sounds like I can take my best guess at data-types in the beginning and then just modify them later if need be, without wasting too much time up front?

TomTees

no, it isn’t :slight_smile:

Ha ha. True. Hopefully I won’t make any big mistakes like that.

Say, do you ever “super-size” your columns and then after collecting a fair amount of data put them “on a diet” and shrink them down to a more relevant size?

For example, maybe set “Address” to VARCHAR(100) and then after a while if it looks like addresses do no exceed a length of 50 resize things to VARCHAR(50).

Or maybe set a money field to DECIMAL(8,2) and then downsize it to DECIMAL(5,2) if you see $$$ figures that never exceed $999.99

TomTees

The change to that address field would have no effect whatsoever until such time as someone actually needs to enter an address longer than 50 chars.

Part of designing the database in the first place is determining how big the content for the various fields can be so if you have a decimal(8,2) field then presumably it is because amounts in the 100,000 range are going to be needed at some point and on the day after you decide to reduce the field to 5,2 would probably be the day someone first needs to enter 100,000 and because of your change they end up with 999.99 instead.

in my opinion, yes

however, note that “just modifying them later” isn’t always as easy as it sounds, and once you have accumulated some data, changing it to a different format might prove problematic

example: suppose you had a table for people, and you designed it to have a user id, a password, a fullname, and a contact email

and away you go, collecting people’s names like Joe Smith, Todd Kamalfezchuk, Cher, Montegue Q. Flimsworth III, and so on

and then you find, for whatever reason, that you need separate firstname and lastname columns instead of a single fullname column

you’re SOL on making that conversion, aren’t you

:slight_smile: