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

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.