Dynamic Database table attributes (best practice)

I want to create a DVD Database (MySQL) with custom fields, that means, that the user should be able to add his own information fields to catalogue his DVDs. I just provide the required attributes, for example:

 - ID
 - Title

The user can add fields for his own use like

- Actors
- Year
- Genre
- Plot
- etc

Now, what's the best practice for this challenge? I've got some ideas:

  1. Alter the table and add attributes (possible data loss)
  2. Use temporary tables (less performance)

Is there a better idea?


ANSWERS:


I would use another table that contains dvd_id, field_name, value and store all of your custom fields there. This keeps you from having to alter the table every time a new field is added or removed.


if i were to do that task i would make a layout like

movie move_id | title

movie_has_attribute movie_id | attribute_id

attribute attribute_id | name | value

like this you can get all attributes and values by the movie_id with a join statement. I hope i got your question right.


You should try out MySQL 5.7 and its JSON data type.

You can use this to define a semi-structured collection of user-defined attributes.

CREATE TABLE Dvd (
  id SERIAL PRIMARY KEY,
  title text NOT NULL,
  attr JSON NOT NULL
);

INSERT INTO Dvd
SET title = 'Wonder Woman', 
    attr = '{"Actors": ["Gal Godot", "Chris Pine"],
             "Year": "2017",
             "Genre": "Action",
             "Plot": "See Captain America: The First Avenger"
            }';


 MORE:


 ? Designing a database for my students
 ? Java database management system to handle nested/tree data
 ? Database design; Dynamically creating tables
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic module import in Python
 ? Dynamic loading of python modules
 ? how to "reimport" module to python then code be changed after import
 ? free of doubly linked list