January 11th, 2012, 10:43 PM
Data Type Sheet Cheat / Guide?
I'm extremely new to SQL and I have no programming language experience whatsoever. I've only been ANSI SQL for about a year so basic SQL is fairly easy as far as SELECT, INSERT, & UPDATE statements but my pro blem is not understanding under what circumstances to use a specific data type. Is there a guide, rule of thumb, or some way I can learn when the appropriate data type should be used?
For example, I just found out today I should never create a datatype of integer for a field storing zip codes.
January 11th, 2012, 11:06 PM
I think that answer to that is... use the most relevant data type. As you've found out, that's not as easy as it sounds sometimes.
The most general guide to that sort of thing is store numbers in numeric fields, dates and times in date/time fields, booleans in boolean fields (if available), and most other things can be stored as text.
Most times it will come down to what you need the data for and how you need to use it. The post (zip) codes are a good example for this. Where I am, post codes are only ever 4 numbers and there's no exception to this, so if I was only wanting local users then I could set up a numerical data type without any problems. However if I needed to look at international users then I couldn't do that because the allowable lengths change and the valid characters change from country to country and sometimes even within countries.
Just know your data. Know what you need to store, know what the possible values can be, and work with that. If you don't know, get an answer from someone that does. If you can't do that, opt for the type with the highest probability of handling everything that you might need.