用户数据的数据库设计
我正处于一个新项目的早期阶段。由于我们正在迭代且相对快速地开发（边走边设计产品），有时为某些东西选择“正确”的设计可能会有点困难。我们倾向于选择一些东西并在必要时进行重构。
现在我正在研究用户数据模型。我的方法是基本上有两个表：一个包含基本的登录类型数据（用户名、创建日期、凭据等），另一个表用于存储我们需要与用户关联的键、值数据。
这使我们能够在早期阶段非常灵活地决定向用户存储哪些数据。如果我们不需要对数据进行复杂的查询（目前还不需要），那么这可以实现良好的可扩展性。
这也是我之前用过的一个模式。
我的一个大问题是，为什么从长远来看这是一个糟糕的设计？
I am at the early stages of a new project. As we are developing iteratively and relatively quickly (designing the product as we go), sometimes it can be a little bit harder to pick the "right" design for something up front. We tend to pick something and re-factor whenever necessary.
Right now I'm working on the model for user data. My approach is to have essentially 2 tables: one with essential login type data (username, created date, credentials, etc), and the other table to store Key, Value data we need associated with users.
This allows us to be very flexible in the early stages regarding what data we are storing with users. Provided we don't need complex queries on the data (which we don't yet), this allows for good scalability.
This is also a pattern I have used before.
My big question is, why is this a bad design for the long run?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该问的问题是：
if (Key=="something")）？如果您可以提前为所有可能的键设计应用程序，并且您的应用程序以特定方式处理所有这些键，那么您应该只向“主”表添加适当的列，并完全停止使用“键值”表。
如果您可以预测所有键，但对其中一些键进行一般性处理，则可以保留您的结构，或者您可以将专门处理的那些键移至“主”表的列中，并将其余键保留在“键值”表中。
如果您无法预测所有可能的键（即用户将能够添加自己的键），并且即使您可以预测的键也始终以通用方式处理，请保留当前结构。
The questions you should ask are:
if (Key=="something")anywhere in your code)?If you can design your application in advance for all possible keys and your application is treating them all in a specific way, you should just add appropriate columns to the "main" table and stop using the "key-value" table altogether.
If you can predict all keys but you treat some of them generically, you may keep your structure, or alternatively you may move those keys you treat specially into columns of the "main" table and leave the rest in the "key-value" table.
If you cannot predict all the possible keys (i.e. users will have ability to add their own) and even those you can are always treated in a generic way, keep the current structure.
正如您自己所说：
“只要我们不需要对数据进行复杂的查询（目前还不需要），这就可以实现良好的可扩展性。”
因此，一旦您需要“复杂”查询无论是在运行时间还是正确编写查询本身所需的时间方面，这都会变得效率低下。
也许您可以逐步解决这个问题，并在给定子集“野外”使用足以保证其稳定后立即将一些键值对迁移到实际表字段？
一般来说，关系模型有其优点，管理大量键值“伪字段”并不是其中之一。为此，您可能想要使用 NOSQL 产品。
As you said yourself:
"Provided we don't need complex queries on the data (which we don't yet), this allows for good scalability."
So as soon as you need "complex" queries this will become inefficient, both in terms of elapsed time, and the time required to correctly write the queries themselves.
Maybe you can approach this piecemeal, and migrate some of the key-value pairs to actual table fields as soon as a given subset has been used "in the wild" enough to guarantee it's stable?
In general, the relational model has its strengths, and managing large sets of key-value "pseudofields" is not one of them. For that you may want to go to NOSQL products, maybe.