juricta
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
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
PS: that is called relational table design. Much more flexible
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
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):
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.
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
)
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.
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+)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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