How should I store this in MySQL?

I am planning on creating a web interface, in which users will create fields to store information from robot matches.

Each开发者_运维技巧 year the competition is different, and thus the information that needs to be recorded is different. Usually there are 5-15 pieces of information for each of the 6 robots on the playing field.

I had a couple of thoughts,

1.) take the input from a php page, and use it to create a table in the database.

2.) To somehow store the schema as a text string, and the same with the data.

Your thoughts and insight would be greatly appreciated.

If the information is straight forward, why not have one table with a schema like:

ID , year , user_id , attrib_name , attrib_value

Now users can click on Add Field on the form, 2 text boxes show up. In one they put in the name of the attribute and the other contains the info.

This way they can add as much info as they want and based on your architecture you can adjust the table to have something other than user_id , year as the identifiers for yearly entries.

Hope this helps!

If you are going to need many different fields, and they are all a string, you could create a robot_info table, and then a relation table with that. for example:

CREATE TABLE robot (
  robot_id int not null default 0 primary key auto increment,
  //stuff about the robot
);

CREATE TABLE robot_info (
  robot_info_id int not null default 0 primary key auto increment,
  field_type varchar(255),
  field_value varchar(255)
);

CREATE TABLE robot_info_relation (
  robot_id int,
  robot_info_id int
); 

This way any robot can have any number/combination of fields of info.

See this question about Atribute-Value model : entity-attribute-value-database-vs-strict-relational-model-ecommerce-question

if the value domains overlap from year to year at all, then just make them all attributes on the table, then only populate the known ones in any given year.