涉及动态字段的数据库结构 [英] Database Structure involving dynamic fields
问题描述
我在一个项目上工作。它主要用于学习，我发现实际上尝试一个复杂的项目是在掌握基础知识之后学习语言的最佳方式。数据库设计不是一个强项，我开始阅读它，但它的早期和仍然在学习。
Im working on a project. Its mostly for learning purposes, i find actually trying a complicated project is the best way to learn a language after grasping the basics. Database design is not a strong point, i started reading up on it but its early days and im still learning.
这是我的alpha模式，我真的在我只是试图记下我可以想到的一切，看看是否有任何问题跳出来。
http://diagrams.seaquail.net/Diagram.aspx?ID=10094#
Here is my alpha schema, im really at the point where im just trying to jot down everything i can think of and seeing if any issues jump out. http://diagrams.seaquail.net/Diagram.aspx?ID=10094#
我有一些担心，我想反馈：
Some of my concerns i would like feedback on:
注意核心属性，如区域例如让我们说简单的地方是厨房，卧室，花园，浴室和客厅。对于可能是首页，联系页面，about_us，启动屏幕的另一个客户。它可以是2个区域，可以是100个，不需要限制。
Notice for the core attributes like area for example, lets say for simplicity the areas are kitchen,bedroom,garden,bathroom and living room. For another customer that might be homepage,contact page,about_us,splash screen. It could be 2 areas and it could be 100, there isn't a need to limit it.
我为默认值创建了单独的表，每个都链接到一个错误。后来我来到了自定义字段的问题，如果有人想要举例说明哪个主题的错误适用于我们没有这个，可能还有其他100件事情，所以我想坚持一套核心的属性和自定义字段给人的灵活性。
I created separate tables for the defaults and each is linked to a bug. Later i came to the problem of custom fields, if someone wants for example to mark which theme the bug applies to we dont have that, there is probably a 100 other things so i wanted to stick to a core set of attributes and the custom fields give people flexibility.
然而，当我到达自定义字段时，我知道我有一个问题，我不能为每个自定义字段创建一个表，所以我改为使用2个表。自定义字段和custom_field_values。这个想法是每个字段，包括默认值将被存储在这个表中，并且每个字段将被链接到只有这样的
However when i got to the custom fields i knew i had an issue, i cant be creating a table for every custom field so i instead used 2 tables. custom fields and custom_field_values. The idea is every field including defaults would be stored in this table and each would be linked to the values table which would just have something like this
custom_fields table
id project_id name
01 1 area(default)
12 2 rooms(custom)
13 4 website(custom)
custom_field_values table
id area project_id sort_number
667 area1 1 1
668 area2 1 2
669 area3 1 3
670 area4 1 4
671 bedroom 2 1
672 bathroom 2 2
673 garden 2 3
674 livingroom 2 4
675 homepage 4 1
676 about_us 4 2
677 contact 4 3
678 splash page 4 4
这看起来像一个有效的方式来处理这样的动态字段，还有其他替代方案吗？
Does this look like an efficient way to handle dynamic fields like this or is there other alternatives?
默认值是硬编码，因此您可以使用它们或替换为自己的，或者我可以创建另一个表，以允许用户编辑将与其项目相关联的默认值的名称。任何反馈都是受欢迎的，如果有一些非常明显的问题在该计划中，请随时批评。
The defaults would be hard coded so you can either use them or replace with your own or i could create a another table to allow users to edit the name of the defaults which would be linked to their project. Any feedback is welcome and if there something very obvious with issues in the scheme please feel free to critique.
推荐答案
你已经重新发明了旧的反模式称为实体属性值。表中的自定义字段的想法在逻辑上与关系数据库不兼容。 关系具有固定数量的字段。
You have reinvented an old antipattern called Entity-Attribute-Value. The idea of custom fields in a table is really logically incompatible with a relational database. A relation has a fixed number of fields.
但是即使它不是正确的关系，我们仍然需要这样做。
But even though it isn't properly relational, we still need to do it sometimes.
有几种方法来模拟自定义字段SQL虽然大部分都违反规范化规则。有关一些示例，请参阅：
There are a few methods to mimic custom fields in SQL, though most of them break rules of normalization. For some examples, see:
- Product table, many kinds of product, each product has many parameters on StackOverflow
- My presentation Extensible Data Modeling with MySQL
- My book SQL Antipatterns: Avoiding the Pitfalls of Database Programming
这篇关于涉及动态字段的数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
