Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120

    Data type and standard sizes

    Where can I find a list of the standard accepted sizes for data types (length of strings particularly) like first Name varchar(25) email(35),

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Never heard of such a thing.

    Just use char(255) for all strings and float for all numbers. Or better yet, use an XML datatype and you can store anything in it.







    ....I'm KIDDING guys! I'm just kidding!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    blindman was goofing about.

    You just use the smallest size that is likely to not cause you problems. There is certainly no official or supported standard. Maybe someone somewhere has published their standards.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Well in that case,

    What would you recommend for max
    Email
    and Geographic Location (Google maps Style)

    I can make estimates on the size but I really want to get it spot on,

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Nate1
    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 have no idea about the second one.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    We recently encountered truncation issues with
    Firstname varchar(20)
    Surname varchar(20)
    When some clever chap decides to go get a really long surname and join the company
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Email addresses
    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
    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.

    Perhaps ...
    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

    Mike

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by mike_bike_kite
    Llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch
    If you've ever seen a street sign for the place (other than the one at the train station there... they're might proud...

    Then it's written as "Llanfairpwllgwyngyll" which is a unique place name.
    Incidently, Llanfairpwllgwyngyll has the longest single-barreled place name in the world

    I think ultimately, just be sensible, don't just make everythign Varchar(8000(!!
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another scenario that mysql takes care of for ya

    if Surname is VARCHAR(20) and you insert a 50-character name, it lets you!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you consider that a desirable feature?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it and see what happens, then you may take a stab at guessing whether i think its actions are desirable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

    Code:
    create table Users (
       id               int,
       full_name        varchar(60),
       first_name       varchar(20),
       last_name        varchar(30),
       email            varchar(60),
       telephone        varchar(20),
       address_line_x   varchar(100),
       post_code        varchar(40)
    )
    I had full_name here as well so we could at least set a standard for all likely fields.

    Mike

    EDIT: added post code (same as zip) and changed numbers for first and last name
    Rudy : postal code should be 40 to encompass portugal's
    Last edited by mike_bike_kite; 08-17-07 at 11:38.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    sadly I have got no further than installing the engine & tools yet. I will give it a go when I get chance.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    sadly I have got no further than installing the engine & tools yet. I will give it a go when I get chance.
    sigh

    i shall let you know now, then, and you can confirm at your leisure

    mysql does not give an error like other databases do ("string or binary data would be truncated") -- instead, it just goes ahead and truncates

    good arguments for both approaches
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •