How to store key value pairs in MySQL?
I am new to databases and MySQL
in particular. Suppose I need to store flat key value data structures in MySQL
. Each data structure contains several required fields and a number of optional fields that are not known in advance and may change frequently.
I will need to restore all data structures with one of the required fields and possibly delete them.
So I would like to store these data structures in a table like this: (just copy-paste from internet, not 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 do I determine primary key
for the second table?
source to share
For the second table, I would recommend:
- adding an explicit auto-incrementing primary key.
- declare length
varchar
- to announce
my_data_structure_id
- has a limitation
unique
The result looks something like this:
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 guess the only limitation is on the pair. However, if you just need one field with a given name, exclude the value from the constraint unique
.
source to share
I often warn about the dangers of EAV, but I am not saying that it is EVIL. It's just fundamentally non-relational, so using a language like SQL that is designed to store and query relational data will always be awkward and inefficient.
Use EAV if there is no other option, but be warned that you are committing yourself to more work when you use EAV. Your queries will be more complex, you will lose the database server's ability to enforce constraints, etc.
An alternative is to use some type of non-relational database, such as a document store, so a set of user-defined fields can be inserted as needed.
MySQL provides a JSON datatype , so you have a hybrid mode where you can use regular columns with SQL datatypes for the attributes you always need and then JSON for dynamic attributes.
source to share