5 ms·
I think more important than obsession over column sizes (in most databases, varchar columns only use the space necessary, not the maximum, more or less) is usin
by ams6110 15y ago
I think more important than obsession over column sizes (in most databases, varchar columns only use the space necessary, not the maximum, more or less) is using correct datatypes. E.g. using varchar for a column that you know will only have numbers. Or using int for timestamps (e.g. using the unix epoch offset) and then having to do a lot of converting. Or sticking serialized objects from your application domain into blob columns.
Most databases have a lot of built in safety and functionality if you are using the correct datatypes, but that is something you can't benefit from if you are treating everything like a blob or a string.
- deleted 15y ago[deleted]
- InclinedPlane 15y agoA rule of thumb: if you ever end up with a situation where you want to display data that is essentially just a table and you can't drive that via a fairly simple sql query then that probably means you need to revisit your design.
- noblethrasher 15y ago"using varchar for a column that you know will only have numbers" I've been bitten too many times by the recommendation of using numeric types for [0-9]+ data. Unless it's fundamentally going to involve arithmetic I say leave it as some kind of string type.
- jbigelow76 15y agoI've got to disagree about not using varchar for a column you you know will only contain numbers. I'm assuming you mean something like the numbers of a phone number (dashes and parens have been scrubbed out). Even if a field will only contain numbers I won't use a numeric based field type unless there is the possibility that I would be using the value in some form of mathematical function. I might add and subtract from an account balance but I would never add or subtract from a Social Security Number or zip code. (I don't keep to this rule for surrogate keys which will be int/bigint auto incrementing, even though I won't be doing any math on the values themselves)
- onemoreact 15y agoThere is value in what your saying. But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Use it as an index and you waste that space again, store it in a cache and you have less space for useful information, compare it with another SSN and it's slower etc. Now individually it's probably irrelevant but start doing this with several fields on a large application and you can noticeably slow things down even if the extra disk space seems cheap. PS: 95% of the time it's probably the safest option, but it's still something you should consider based on how that data fit's in with the rest of your application.
- kls 15y agoSome numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.
- Tsagadai 15y agoPhone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.
- ListMistress 15y agoI agree, neither phone numbers nor social insurance numbers are unique. But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes. This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.