Change tinyint to smallint? 1

[GWHicks]

We had an outside source convert our MSAccess database into a MySQL database with a webbased front end powered by Java. The system is working well, but I was just doing some work in it and see that he has defined the field holding our employee numbers as an unsigned tinyint. This will limit us to a maximum of 255 employee numbers assigned before it is full. Is there any problem with my just changing that field to a smallint or some other type to hold a larger number? Right now all employee numbers are between 1 and 215 so I would imagine there should be no issue with making the conversion. It has always been my understanding that changing to a larger capacity type is fine, but you would run into problems if I were to say have it currently set as smallint and wanted to change to tinyint, in that case I would run into the problem where any current records with a value out of the range limit of the tinyint would then be bad records (or some other corruption would occurr).

Could someone confirm or deny my thinking? Can I just change the datatype to smallint from tinyint without other issues?

Thanks.
Greg Hicks

Greg Hicks
VB.Net (Newbie) Programmer

http://www.ajlb2.com

 

[sleipnir214]

Yes, you can change a column's type to a column of the same type but with greater bit-width. To maximize use of a tinyint, I would convert it to an int unsigned.

But just out of curiosity, why store employee ids as numbers at all? Technically, the ids are just strings that happen to contain only numerical characters. All you're doing is using them as lookup indeces -- I mean, you're not going to perform mathematical operations on the ids, are you?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GWHicks]

Actually you are correct, there would never be a need to process mathematical computations on them. Would you then recommend using a different type for them? varchar(3) maybe?
Thanks for the answer, you must just troll these boards all day long! The last couple questions I have asked have been answered by you, and usually within mere minutes of my posting!
I need to remind myself to go back and mark the posts as helpful (that increases your rank on the boards doesn't it?)
Thanks for all the great advice.
Greg Hicks

Greg Hicks
VB.Net (Newbie) Programmer

http://www.ajlb2.com

 

[sleipnir214]

I would use maybe varchar(4) or varchar(5), if for no other reason that I like for my data to have a little elbow room.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!