How to store key value pairs in MySQL?

I frequently warn about the hazards of EAV (Entity–attribute–value), but I don't say it's EVIL. It's just fundamentally not relational, so using a language like SQL that is designed to store and query relational data is always going to be awkward and inefficient.

Use EAV if there's no other option, but be warned that you're obligating yourself to more work when you use EAV. Your queries will be more complex, you lose the ability for the database server to enforce constraints, and so on.

An alternative is to use some type of non-relational database, like a document store, so you can insert a set of user-defined fields as needed.

MySQL provides the JSON data type, so you have a sort of hybrid mode where you can use conventional columns with SQL data types for attributes you always need, and then JSON for dynamic attributes.

For the second table, I would recommend:

• adding an explicit auto incrementing primary key.
• declare the length of the varchar
• declare my_data_structure_id
• have a unique constraint

The result is something like:

CREATE TABLE my_optional_fields (
   my_optional_fields_id int auto_increment primary key,
   my_data_structure_id int not null,
   my_optional_field_name VARCHAR(255)  NOT NULL,
   my_optional_field_value VARCHAR(255)  NOT NULL,
   FOREIGN KEY (my_data_structure_id) REFERENCES my_data_structures(my_data_structure_id)
   UNIQUE (my_data_structure_id, my_optional_field_name, my_optional_field_value)
);

I am guessing that the unique constraint is on the pair. However, if you just want one field of a given name, exclude the value from the unique constraint.