Best Data Types

[F.Danials]

Hello,

 

I'm new to databases and MySQL, and would very much like to know, what the most efficient/suitable Data Types would be used with the following data:?

 

Account Details:

 

• Email Address
  
• Password
  
• Email Updates?
  
• Interested Sites (6 Check Boxes Max)
  

 

Personal Details:

 

• Title (Mr, Mrs, Miss, Ms, Dr)
  
• First Name
  
• Last Name
  
• Image Title
  
• Upload Image
  

Address:

 

• Property Name/Number
  
• Street Name
  
• Location
  
• Town/City
  
• Country
  
• Postcode
  
• Telephone
  
• Work
  
• Mobile
  

[scarhand]

you could use char or text for all of em

[corbin]

I'm not the best at DB design ever, but I would probably do something like this:

 

You might should look at

http://dev.mysql.com/doc/refman/5.0/en/data-types.html

and

http://dev.mysql.com/doc/refman/5.0/en/choosing-types.html

 

 

Account Details:

 

    * Email Address varchar(50)

    * Password char(32) (or some other number that an encryption algorithm would return)

    * Email Updates? Errr?  Huh?  You mean the number of times someone has updated their email?  smallint

    * Interested Sites (6 Check Boxes Max) It would depend how you would store this....  You could store it as binary (or decimal for that matter.... and use bitwise functions to extract the needed info...)  So probably smallint also.... maybe tinyint.

 

 

Personal Details:

 

    * Title (Mr, Mrs, Miss, Ms, Dr) ENUM('Mr', 'Mrs', 'Miss', 'Ms', 'Dr')

    * First Name varchar(16) or some reasonable length for a first name....

    * Last Name same as first name

    * Image Title errrr???  Huh?  Like....  Image description?  I would go with varchar(255) personally, unless you wanted to allow more, then I guess you could use text or something

    * Upload Image varchar(255)

 

Address:

 

    * Property Name/Number  Err....  Not sure what this would hold.....  Probably some form of varchar

    * Street Name varchar(64)

    * Location What do you mean by location?

    * Town/City  varchar(32)

    * Country Country code or full name?  Either char(2) or varchar(32)

    * Postcode As in zip code?  Or what is this?  int(10) just to be safe?  maybe less?

    * Telephone I would strip out the hyphens (example 555-555-5555 to 5555555555) and store it as an int(10)

    * Work see telephone

    * Mobile see telephone

[fenway]

Don't store postcodes or phone numbers as INTs.

Don't use TEXT fields.

And make sure you pick reasonable lengths.

[F.Danials]

Email Updates = Do you want to received email updates? e.g. Weekly Newsletter

Interested Sites = Websites of Interest, options displayed using check boxes. Multiple choices can be selected.

Location = Shouldn't be in there. My Mistake

 

Why shouldn't Postcodes and Phone numbers be stored as ints?

Why shouldn't I use TEXT fields?

 

Cheers

[fenway]

Why shouldn't Postcodes and Phone numbers be stored as ints?

Because not all postal codes are number, and phone numbers have meaningful substrings.  Since you'll never be able to meaningfully sort by either, there's no point.

 

Why shouldn't I use TEXT fields?

Because it's very poor w.r.t. performance.