if I was to use char 255, and I only needed 35 characters I would be wasting 220bytes of space? then times 10,000 records, thats 2.2 MB for One string, That makes sense. Looking for say I want to store an email what would be the maximum size I would want.
I can make estimates on the size but I really want to get it spot on,
I think that's the point - there is no such thing. I can pretty well guarentee that I can create an email address that is longer than any "reasonable" length you are likely to make that field.
Adding extra length to a varchar is cheap. Consider perhaps 40-45 characters for email. This is long but I think better to have a varchar(45) which contains at longest a 34 character string than have to change the schema later when someone is unable to enter their email address.
I found this which suggests 64 char for name and a max off 255 for the domain. They also mentioned that the name part may expand to 128 characters. So the maximum size might be 128 + 1 + 255 = 384 chars. Also this was written in 1998 so things may of expanded more so now. However I can't imagine anyone having such an address.
So your choice is do you
look at the actual maximum size allowed for an email and cover that ie varchar(384).
decide a rational size for emails ie varchar(64) and then get someone arrive with a longer email address that invalidates your system.
accept that you're guessing and simply go for varchar(255) and then use your valuable thinking time on a more interesting part of your design.
Place names can can get pretty long around the world:
Llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch (in wales)
Tetaumatawhakatangihangakoauaotamateaurehaeaturipu kapihimaungahoronuku pokaiwhenuaakitanarahu (in New Zealand)
Krung-thep-maha-nakorn-boworn-ratana-kosin-mahintar- ayudhya-amaha-dilok-pop-nopa-ratana-rajthani-burirom-udom-rajniwes-mahasat-arn-amorn-pimarn- avatar-satit- sakattiya-visanukam (in Thailand)
and I guess if tourists continue to flock to the place with the longest names then these names will only ever get longer. Whether anyone actuually comes from these places is another question. Thank god for post (zip) codes.
Just use char(255) for all strings
blindman may of hit the nail on the head even in jest
Originally Posted by Nate1
if I was to use char 255, and I only needed 35 characters I would be wasting 220bytes of space?
Varchars only use the space required for the string (+1 char) so you won't be wasting anything.
Having some strict limitions in your database will at least screen out users with double barreled names, strange yocals and wierd geeks which can't be a bad thing
Come on guys - here you have the chance of setting up a world standard for user fields and you're all avoiding the original question - what size should the fields be? I've put in some starting values to get you going