How to store key value pairs in MySQL?

Question

I am new to databases and MySQL in particular. Suppose I need to store flat key-value data structures in MySQL. Each data structure has a few required fields and a number of optional fields that are not known in advance and may change frequently.

I will need to retrieve all data structures by one of the required fields and probably delete them.

So I'd like to store these data structures in a table like that: (just a copy-paste from the Internet rather than working code)

CREATE TABLE my_data_structures (
   my_data_structure_id INT     NOT NULL,
   my_required_field1   VARCHAR NOT NULL,
   my_required_field2   INT     NOT NULL,
   PRIMARY KEY (my_data_structure_id)
)

CREATE TABLE my_optional_fields (
   my_optional_field_name  VARCHAR  NOT NULL,
   my_optional_field_value VARCHAR  NOT NULL,
   FOREIGN KEY (my_data_structure_id) REFERENCES my_data_structures(my_data_structure_id)
)

Does this approach make sense ? How to define the primary key for the second table ?

Answer 1

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.

Answer 2

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.