在MySQL中存储出版物元数据的最佳实践[PHP]

I have a website of publications with various types (Normal, Type 1, Type 2) and each type requires special data.

我有一个各种类型的出版物网站(普通,类型1,类型2),每种类型都需要特殊数据。

My question is:

我的问题是:

What is the best way to store these "special data" in the database?

在数据库中存储这些“特殊数据”的最佳方法是什么?

1) Create a table for each type of publication.
2) Create a table "posts_metadata" with the columns "post, key, value" and save all types in them.
3) create a "metadata" column in the "posts" table and save the "specials data" in them using JSON format.
4) create a "metadata" column in the "posts" table and save the "specials data" in them using XML format.

1)为每种类型的发布创建一个表。 2)创建一个表“posts_metadata”,其中包含“post,key,value”列,并保存所有类型。 3)在“posts”表中创建“元数据”列,并使用JSON格式将“特殊数据”保存在其中。 4)在“posts”表中创建“元数据”列,并使用XML格式将“特殊数据”保存在其中。

What do you think is the most efficient and practical option?

您认为最有效和最实用的选择是什么?

Edit #1:

Example of data:

数据示例:

Normal: without "special data"

正常:没有“特殊数据”

Type 1:

'trademark' => 'Volkswagen'
'model' => 'gol 1.6'
'color' => 'white'
'doors' => '3'
'kms' => '17000'
..etc..

Type 2:

'color' => 'green'
'size' => '15x10'
'status' => 'new'
..etc..

...More Types...

1 个解决方案

#1

If you want to be able to query onto those special data fields choose option 2.

如果您希望能够查询这些特殊数据字段,请选择选项2。

If you only want a storage for the data choose option 3. Depending on the possible size of the data I would create an extra table for it and depending on your version of mysql give the column the json-datatype, if you need the validation etc. https://dev.mysql.com/doc/refman/5.7/en/json.html.

如果你只想要数据的存储选择选项3.根据数据的可能大小,我会为它创建一个额外的表,并根据你的mysql版本给列json-datatype,如果你需要验证等.https://dev.mysql.com/doc/refman/5.7/en/json.html。

Do not use option 4, in comparison to json xml creates too much overhead and adds no benefit in this scenario.

不要使用选项4,与json xml相比,创建了太多的开销,并且在这种情况下没有任何好处。

#1