[thelist] mysql table construction - help selecting field types

Dunstan Orchard dunstan at 1976design.com
Sat Aug 30 08:10:12 CDT 2003

Hi there,

This is another "can someone help me build my MYSQL table" plea.

I'm never sure, no matter how much I check the manual, what field types and 
sizes to assign when building my tables.

Below I've listed the field names and the approximate length
and type of the data being entered. I've also listed the sql for building my 

Could anyone match the two up and make the appropriate changes to the sql? If 
so I would hope to able to use this as an example so I don't have to ask this 
question again :o)

This data is coming from a form where people register and pay to attend a 

Name_First - unknown data length (alpha-numeric)
Name_Middle - unknown data length (alpha-numeric)
Name_Last - unknown data length (alpha-numeric)
Name_Business - unknown data length (alpha-numeric)
Postal_Street_Line1 - unknown data length (alpha-numeric)
Postal_Street_Line2 - unknown data length (alpha-numeric)
Postal_City - unknown data length (alpha-numeric)
Postal_StateProv - unknown data length (alpha-numeric)
Postal_PostalCode - max 19 characters (alpha-numeric)
Postal_Country - unknown data length (alpha-numeric)
Telecom_Phone_Number - unknown data length (numeric)
Telecom_Fax_Number - unknown data length (numeric)
Online_Email - unknown data length (alpha-numeric)
Ecom_Transaction_Amount - max ####.## (numeric but with the . symbol)
Ecom_Payment_Card_Type - max 16 characters (alpha-numeric)
Ecom_Payment_Card_Number - max 19 characters (numeric)
Ecom_Payment_Card_ExpDate_Month - max 2 chacters (numeric)
Ecom_Payment_Card_ExpDate_Year - max 2 chacters (numeric)
Ecom_Payment_Card_Name - unknown data length (alpha-numeric)
seminar1 - unknown data length but < 100 characters (alpha-numeric)
seminar2 - unknown data length but < 100 characters (alpha-numeric)
seminar3 - unknown data length but < 100 characters (alpha-numeric)
seminar4 - unknown data length but < 100 characters (alpha-numeric)
seminar5 - unknown data length but < 100 characters (alpha-numeric)
seminar6 - unknown data length but < 100 characters (alpha-numeric)
seminar7 - unknown data length but < 100 characters (alpha-numeric)
seminar8 - unknown data length but < 100 characters (alpha-numeric)

CREATE TABLE seminar_registration (
  reg_id int(5) NOT NULL default '0',
  name_first text NOT NULL,
  name_middle text NOT NULL,
  name_last text NOT NULL,
  name_business text NOT NULL,
  postal_street_line1 text NOT NULL,
  postal_street_line2 text NOT NULL,
  postal_city text NOT NULL,
  postal_stateprov text NOT NULL,
  postal_postalcode tinytext NOT NULL,
  postal_country text NOT NULL,
  telecom_phone_number text NOT NULL,
  telecom_fax_number text NOT NULL,
  online_email text NOT NULL,
  ecom_transaction_amount decimal(10,0) NOT NULL default '0',
  ecom_payment_card_type text NOT NULL,
  ecom_payment_card_number tinyint(19) NOT NULL default '0',
  ecom_payment_card_expdate_month tinyint(2) NOT NULL default '0',
  ecom_payment_card_expdate_year tinyint(2) NOT NULL default '0',
  ecom_payment_card_name text NOT NULL,
  seminar1 text NOT NULL,
  seminar2 text NOT NULL,
  seminar3 text NOT NULL,
  seminar4 text NOT NULL,
  seminar5 text NOT NULL,
  seminar6 text NOT NULL,
  seminar7 text NOT NULL,
  seminar8 text NOT NULL,
  UNIQUE KEY reg_id (reg_id)

Thanks in advance for anyone kind enough to help yet another person out with 
this kind of problem,

Cheers - Dunstan

Dorset, England

More information about the thelist mailing list