How to design one main table and multiple different types of appendices?

For example, there is the following blog: The data stored in this blog is relatively complex
Can store
Movie data:
Music data:
Product data:
Picture data:
Software data:
How to design the table to store so much data?
Each type of data has its own unique characteristics. For example, music has lyricists and composers, and pictures have pixels.
It is impossible to have a table and then give each different characteristic of the data a field.
But if it is divided into tables, there is a general table that records basic information such as type and ID, one table for movie data, one table for picture data, etc... In this case, if you want to retrieve all the data, you need to query a lot. Forming a table results in relatively low efficiency.
Is there any better design solution?

Reply content:

For example, there is the following blog: The data stored in this blog is relatively complex
Can store
Movie data:
Music data:
Product data:
Picture data:
Software data:
How to design the table to store so much data?
Each type of data has its own unique characteristics. For example, music has lyricists and composers, and pictures have pixels.
It is impossible to have a table and then give each different characteristic of the data a field.
But if it is divided into tables, a general table records the basic information such as type and ID, one table for movie data, one table for picture data, etc... In this case, if you want to retrieve all the data, you need to query a lot Forming a table results in relatively low efficiency.
Is there any better design solution?

Can be stored in nosql, such as mongodb document.
No need to stick to fields.

If the questioner is using mysql5.7, you can set the field to store data to the json type, such as

<code>//其他數據字段...


//电影数据，音乐数据等設計爲json
{
    “move”: {
    
    },
    "music" : {
    
    }
    
    //.......
}

</code>

After dividing the table, the logic behind the front and back needs to be written separately:

When reading at the front desk, do not read the main table data directly, but query the main table through associated tables of specific types.

Read the main table information directly in the background, but do not read the attached table. Only the data in the main table is displayed in the background list. If it is necessary to display the data in the attached table, put it on the details page. If you really need to display certain fields in each appendix, then it is worth sacrificing some performance. In addition, if these fields are common to all appendices, they should also be mentioned in the main table

You can refer to the 2nd floor, but if you still want a single type of database, you can refer to WordPress's library design... It designs non-main or variable type attributes into secondary table storage in the form of key-value... The idea is the same as nosql Consistent....just implemented using a relational database

Or you can use EAV structure design, the table only stores data, and add a layer of code on it to meet the needs of different types of data.
Just search EAV for the specific method. Magento is an online store implemented using this method.