SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Sample MySQL databases & Spec's for "standard fields"

    I was poking around the net for a few resources here. Basically, I am jumping on the MySQL wagon full steam. Just bought a license for navicat and I am ready to go.

    What I am trying to determine ( this isn't just in mysql ) but more in general. I have a lot of contact information going in this and I just am trying to find the standard field definitions a pro would use to store things such as:

    Address1,Address2, City, County, State, Zip and Phone Numbers. In other words nothing mind blowing, but I would like to have some sample setups to check against.

    If anyone can direct me or mail me some sample .sql files to create myself a few databases, it would just make my day. I have spent a lot of time trying to find exactly what I am looking for here but just cant..

  2. #2
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    I don't really know the standard for those things but I would guess:

    Address1 VARCHAR(100)
    Address2 VARCHAR(100)
    City VARCHAR(45)
    County VARCHAR(45)
    State VARCHAR(2)
    Zip VARCHAR(5)
    Phone VARCHAR(10)

    The phone number definition could be changed to include the "-" character, so it would be VARCHAR(12). The zip assumes you only want the 5 digit zip code and not the 9. The state definition assumes you are in the U.S. where states are denoted with two characters.

    Hope that helps!

  3. #3
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Address1 VARCHAR(100)
    Address2 VARCHAR(100)
    City VARCHAR(45)
    County VARCHAR(45)
    State VARCHAR(2)
    Zip VARCHAR(9)
    - I guess I will strip the dashes out. I need the 9 here. Is there any way for it to be longer than that?

    Phone VARCHAR(10)

    Country ( 2 ) -- to store the 2 digit country abbrev.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by MCsolas
    I need the 9 here. Is there any way for it to be longer than that
    are you asking whether us zip codes can exceed 9 characters? no

    or are you asking whether it might be wise to set it larger, just in case you want to store postal codes that might be larger, in, say, other countries?

    you can set it as large as varchar(255) and if all you store in it are fields of length 5 or 9, you still haven't given up anything, nor are you taking up more room than necessary (9 bytes in a varchar(9) field occupies exactly as much, no more, no less space than 9 bytes in a varchar(255) fields) nor is it less efficient

    for internationalization or "i18n" considerations, see Usable Forms (for an international audience) or do a google search for i18n
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    are you asking whether us zip codes can exceed 9 characters? no
    Yes that is what I was asking but I will heed your advice on the 255 limit. Why make the database throw an error when you dont have to. I prefer a flexible layout in my DB. ( especially coming from access - hey we all have to learn somewhere hehe ) I got used to having problems when attempting to match things specifically, I found that it just didn't work that way. I can just catch what I need in the CFM code I am using and thats that.

    that link is very relative to what I am working on. I see its one of the highest rated articles .. and I remember reading this guys articles before as well.

    MUCH THANKS. !!


Bookmarks

Posting Permissions

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