How should I store this in MySQL?

Question

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.

Using single data field and use delimiter to seperate data or you could store field name and data within delimiter. All depend on volume of data and no of fields. — Senthil, Apr 12 '11 at 22:52

score 2 · Answer 1 · answered Apr 12 '11 at 22:52

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!

score 1 · Answer 2 · answered Apr 12 '11 at 22:51

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.

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

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

edited May 23 '17 at 12:26

Community

1
1

answered Apr 12 '11 at 22:50

ypercubeᵀᴹ

113,259
19
174
235

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – ProgramFOX Mar 08 '14 at 10:47

score 0 · Answer 4 · answered Apr 12 '11 at 22:52

0

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.

answered Apr 12 '11 at 22:52

Randy

16,480
1
37
55

How should I store this in MySQL?

4 Answers4