Inserting unknown number of rows into a MySQL database

I'm writing an online application form and one of the pieces of data I'm trying to collect is the users education history; school name, school city, years they were there and achievements while there. This data could be collected f开发者_JAVA百科or anywhere between zero to about four different schools and the user will be dynamically adding more fields if required.

My question is how would this be captured within the database since I don't know how many schools the user will be adding? Can it be done in one table, or do I need to create multiple tables and capture the education data in a separate table?

Any help or pointers would be greatly appreciated.

Thanks.

---

You need separate tables. One with a row per person, and another with a row per school. Schools can be related to people using either a personID in the school table, or an additional table representing the "person attended school" relationship.

Any association of one entity (person) with multiple other entities (schools) requires more than one table.A general rule to follow is one table per "entity", plus additional tables for many-to-many relationships.

---

Make a separate table for education, and model an "education" in php as a separate class.

---

The term for this is Normalization. You want to structure multiple tables such that one row of data is succinct, and as little information is repeated as possible. This can be done by figuring out in your head which pieces of information will be repeated, and making a table for that information.

Data between tables is linked via primary keys (hopefully auto increment integers). In this case, you would have two tables: Student and Education. The Student table would have 'StudentID, Name, etc' while the Education table would have 'EducationID, StudentID, SchoolName, SchoolCity, SchoolStudentID, etc'.

Note that Student.StudentID would match with Education.StudentID and can be acquired with the following query:

select Student.*, Education.* from Student left join Education on Student.StudentID = Education.StudentID

In fact, this will have 'SchoolName' and 'SchoolCity' repeating over and over, so this is not fully normalized. To continue, you need to change it a little bit again:

Student: StudentID, Name, etc
School: SchoolID, SchoolName, SchoolCity, etc
Education: StudentID, SchoolID, StudentSchoolID, etc

Note: each student has a different student id at each school they attend - don't confuse this with the auto increment integer 'StudentID'. To get a list in this case:

select Student.*, Education.StudentSchoolID, School.* from
  Student left join Education on Student.StudentID = Education.StudentID
  left join School on Education.SchoolID = School.SchoolID