SQL数据库设计与一些用户定义的字段

时间：2022-12-21 12:33:33

I'm developing a database schema to handle collection of data and later, reporting on this data.

我正在开发一个数据库模式来处理数据收集,然后报告这些数据。

After a requirements discussion, it seems that either an entity-attribute-value (EAV) solution, or a flat table solution would be alright - since the data is somewhat sparse but not highly sparse.

在需求讨论之后,似乎实体 - 属性 - 值(EAV)解决方案或平面表解决方案都没有问题 - 因为数据有点稀疏但不是非常稀疏。

However, user defined fields will become a must in the future, but I understand that querying and optimizing an RDBMS with EAV tables can become complex.

但是,用户定义的字段将成为未来的必备字段,但据我所知,使用EAV表查询和优化RDBMS会变得复杂。

I've taken a look at the discussion here, and I was thinking an option similar to option 1 would be possible. For example, have a number of set fields, then a number of spare fields that users can define the labels of.

我已经看过这里的讨论了,我认为可以选择类似于选项1的选项。例如,有许多设置字段,然后是一些用户可以定义标签的备用字段。

In terms of reporting, is there any downside in using this approach rather than using EAV?

在报告方面,使用这种方法而不是使用EAV是否有任何不足之处?

1 个解决方案

#1

2  

You will regret EAV, especially when it comes to reporting

你会后悔EAV,特别是在报道方面

  1. Make sure you're aware of existing data model patterns before you try anything: Ready to use database model patterns

    在尝试任何操作之前,请确保您了解现有的数据模型模式:准备使用数据库模型模式

  2. Familiarize yourself with Table Inheritance: How can you represent inheritance in a database?

    熟悉表继承:如何在数据库中表示继承?

  3. Consider allowing users to modify their own schemas: https://martinfowler.com/bliki/UserDefinedField.html

    考虑允许用户修改自己的模式:https://martinfowler.com/bliki/UserDefinedField.html

  4. EAV is almost always a really bad idea. If you still need custom fields after trying the above, use a blob type (like JSON or XML) with indexing: http://backchannel.org/blog/friendfeed-schemaless-mysql . Postgres's binary jsonb is fast and allows indexing/querying

    EAV几乎总是一个非常糟糕的主意。如果在尝试上述操作后仍需要自定义字段,请使用带索引的blob类型(如JSON或XML):http://backchannel.org/blog/friendfeed-schemaless-mysql。 Postgres的二进制jsonb速度很快,允许索引/查询

#1

2  

You will regret EAV, especially when it comes to reporting

你会后悔EAV,特别是在报道方面

  1. Make sure you're aware of existing data model patterns before you try anything: Ready to use database model patterns

    在尝试任何操作之前,请确保您了解现有的数据模型模式:准备使用数据库模型模式

  2. Familiarize yourself with Table Inheritance: How can you represent inheritance in a database?

    熟悉表继承:如何在数据库中表示继承?

  3. Consider allowing users to modify their own schemas: https://martinfowler.com/bliki/UserDefinedField.html

    考虑允许用户修改自己的模式:https://martinfowler.com/bliki/UserDefinedField.html

  4. EAV is almost always a really bad idea. If you still need custom fields after trying the above, use a blob type (like JSON or XML) with indexing: http://backchannel.org/blog/friendfeed-schemaless-mysql . Postgres's binary jsonb is fast and allows indexing/querying

    EAV几乎总是一个非常糟糕的主意。如果在尝试上述操作后仍需要自定义字段,请使用带索引的blob类型(如JSON或XML):http://backchannel.org/blog/friendfeed-schemaless-mysql。 Postgres的二进制jsonb速度很快,允许索引/查询

标签：

相关文章