数据库对外关系
我正在开发一个定制且特定的 CMS 项目。在前端，许多字段都会有预先填充的选择。然而，对于这些字段，需要有一个“其他”选项，允许用户输入文本字符串。项目范围不希望将这些新的“其他”值添加到预填充列表中（现在或以后）。这只是一个例外。项目范围坚持这种灵活性，并在整个应用程序中实现。
我有包含所有这些预填充列表的数据库表。我将这些表称为我的列表表（全部以“list_”开头）
我的问题是关于存储用户所做的选择。如果不是这种灵活性，我会将值作为外键存储到相应的列表中。然而，这些字段存储值（字符串）而不是键是有意义的。缺点是索引（次要）、内容控制（次要）、全局更新，即更改列表中的值不会在系统中产生反向影响，除非编码为（相当大的问题）。
我还要提到，将数据存储为值而不是键也使编程和函数变得更加简单（我正在编写一个服务层，它减少了连接并允许函数更加通用）。
存储为值（字符串）而不是键是团队想要走的路线。
我这样做是否犯了一个大错误？或者说这种情况相当普遍？还有其他问题需要考虑吗？
替代方案： 我的替代方法是将“其他”字符串添加为列表中的新行，并使用字段使其“隐藏”。
I am working on a project that is a customized and specific CMS. In the frontend many of the fields will have pre-populated choices. However for these fields there needs to be an "Other" option that allows for a user entered text string. The project scope does not want these new "other" values to be added to the pre-populated lists (now or in the further). It is just an exception. The project scope is insistent on this flexibility which is implemented throughout the app.
I have database tables that contain all these pre-populated lists. I call these tables my list tables (all begin with "list_")
My question is about storing the choices a user makes. If it was not for this flexibility I would store the value as a foreign key to the appropriate list table. However, it makes sense for these fields to store the value(string) rather then the key. The drawbacks are indexing (minor), content control (minor), global updates i.e. changing a value in the list table will not retro-ripple through the system unless coded to (pretty big issue).
I will also mention that storing the data as values and not keys makes the programming and functions much simpler too (I am writing a service layer and it cuts down on joins and allows functions to be more generic).
Store as a value(string) not a key is the course the team is wanting to go.
Am I making a big mistake by doing this? Or is this fairly common? Are there other issues to consider?
Alternatives:
My alternative would be to add the "Other" string as a new row in the lists table and use a field to make it "hidden".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一般来说，如果您可以存储外键而不是值，那么它为您带来的强制一致性通常会让将来的事情变得更简单。
然而，“要求”听起来好像这不是一个选项，这使得它“感觉”好像数据中存在不一致。听起来目标是在一个字段中存储两种类型的数据：预定义值或用户选择的值，并且这两个值具有不同的含义。如果不了解有关系统的更多信息，很难确定，但这可能会使某些类型的查询更难以编写（但并非不可能）。
如果该值确实只是一个文本值，并且预填充的列表提供了一种简单的选择方法，那么存储文本值似乎没问题。然而，这个问题听起来好像查找表中的值附加了意义。如果是这样，那么存储外键可能会产生更稳健的解决方案。但如果选择“其他”，则可能需要将“其他”值（及其关联的外键值）添加到查找表中，并将另一个自由格式文本字段添加到表中以存储实际文本。
In general, if you can store the foreign key rather than the value, the enforced consistency that it buys you will typically make things simpler in the future.
However, the "requirements" make it sound as if that is not an option, which makes it "feel" as if there is an inconsistency in the data. It sounds like the goal is two store two types of data in one field: either a pre-defined value or a user selected value and the two values have different meanings. Without knowing more about the system, it's hard to say for sure, but that could make certain types of queries a little more difficult to write (but not impossible).
If the value is truly just a text value and the pre-populated lists provide an easy way to make choices, then storing the text value seems okay. However, the question makes it sound as if meaning is being attached to the values in the lookup tables. If so, then storing the foreign key may result in a more robust solution. But it may require that an "Other" value (with its associated foreign key value) be added to the lookup table and another free-form text field be added to the table to store the actual text if "Other" is chosen.
首先，您所描述的“列表”表的概念感觉很模糊。这似乎表明有关这些实体的规范尚未得到充分理解或审查。您不需要区分“列表”实体和非“列表”实体。
其次，听起来您遇到的问题是当用户可以选择输入自己的值而不是可能出现在下拉列表中的项目时。在这种情况下，我将有一个表示“自定义”的特殊外键值，然后我将在其中显示一个文本框，供用户输入其自定义值。我会将自定义值存储在与 FK 值不同的单独列中。每当用户选择非自定义选项时，我都会清空自定义条目（理想情况下，这将通过检查约束强制执行，但 MySQL 不遵守它们，因此您必须使用触发器）。这样，您的 CMS 可以简单地首先查看是否有自定义值，然后查看来自 FK 的值。同时拥有 FK 和自定义文本列的优点是，您可以轻松更改父表的构成（添加属性、添加值、调整值等），而不必影响子表。
First, the concept of a "list" table in the way you describe feels nebulous. It would seem to indicate that the specification on those entities is not fully understood or vetted. You shouldn't need to differentiate "list" entities from non-"list" entities.
Second, it sounds like the problem you are having is when the user is given the choice to enter their own value as opposed to a item that might appear in a droplist. In that scenario, I would have a special foreign key value that represents "Custom" where I would then show a textbox for the user to enter their custom value. I would store the custom value in a separate column from the FK value. Whenever the user chose a non-custom choice, I would null out the custom entry (ideally that would be enforced via a Check constraint but MySQL doesn't honor them so you'd have to use a trigger). In this way, your CMS can simply look first at whether there is a custom value and then at the value from the FK. The advantage to having both the FK and a column for custom text is that you can easily change the makeup of the parent table (add attributes, add values, adjust values etc.) without having to affect the child table.