Link to home
Start Free TrialLog in
Avatar of juricta
jurictaFlag for United States of America

asked on

Create a MySQL table as easily as possible

I have MySQL on GoDaddy web site and I want to create a MySQL database called "Retirement" and a TABLE called "report" as easily as possible. Once the majority of fields are added I can go back occasionally and add any additional fields Here are a few of the fields:

NAME: Current1 to Current56, INTEGER, 100xCharacters
NAME: Retirement1 to Retirement56, INTEGER, 100xCharacters
NAME: tot1 to tot56, INTEGER, 100xCharacters
NAME: email, varchar, 255xCharacters, NOT NULL
NAME: id, INTEGER, 11xCharacters, NOT NULL, AUTO INCREMENT (This is the primary key)

Is there an easy way to create this table where I do not have to manually add over 200 fields?  Something like create an EXCEL/TEXT/WORD/etc. file  and import that file?  If so what is the step by step procedure
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

This should likely be designed into seversl tables.
Instead of having x columns, have one table with 2 columns:  the pk value from the main table, and the other value, one row per value.
Adding more values will then not need any additional coding
PS: that is called relational table design. Much more flexible
Avatar of juricta

ASKER

1.  Not sure what you mean by "pk value" nor how the tables would be constructed
2. I once created a table that hade one table with one row per "id".  In order to create this table I had to ADD 12 field for CURRENT (1-12) and I had to ADD 12 field for RETIREMENT (1-12) and so on.  This was very tedious and time consuming.

 I was trying to do the table quickly since I currently have 56 fields NAMED CURRENT (ie Current1, Current 2...Current56).  If I could do this in EXCEL for instance I could type Current1 and then use auto-fill to complete all the Current fields. Then I have to do this all over for the RETIREMENT and TOT fields.  Lots and lots of typing
the 2 tables would look like this (pseudo-code):
create table main_table ( id integer not null autoincrement primar key
, email varchar(255) not null
)

create table related_table ( id_main integer not null foreign key main_table (id )
, current_value integer
, retirement_value integer
, total_value integer
)

Open in new window


in main_table, you insert 1 record with the email (and the id is generated)
in related_table, you insert (up to) 56 records.
at least, this is my understanding from the design you will want. adding more records will be easy, no table change.
Avatar of juricta

ASKER

Guy,
Will this create the FIELDs Current1, Current2...Current56 as well as the other FIELDs I mentioned?  Where would this code you provided go on my page?  Once the table is created I should rarely have to ADD FIELDs but that would not be impossible.  Each data record (id) could possibly have all the FIELDs filled in (all 200+)
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial