数据库设计，空列或添加新列，或者其他
我正在重新设计一款产品。
旧的数据库设计是...... ID、Key、Value、所有字符串，其中值可以是数字、文本或日期。尝试对此数据进行特定类型的搜索是不可能的（或者速度足够慢，这并不重要）。键是对象具有的一组值，因此假设 obj a 的出生日期为 1/12/1969，姓名为 Dan，电话号码为 555-555-5555，在数据库中，这被存储为
出生日期 1/12 /1969年 一个名字丹 电话号码 555-555-5555
我想重新设计它，以便键实际上是字段，这样我们就可以得到
一个 1/12/1969 Dan 555-555-5555
我担心的是，当用户进去并添加一个新密钥，例如手机，添加该列可能需要很长时间和/或让用户感到困惑。
用户很可能会在开始时定义密钥，但有可能在使用产品多年后添加密钥。
我的一个想法是将我们支持的不同类型的几列添加为空，然后将它们重命名为用户添加它们，是的，当我用完这些空列时，我仍然会遇到问题，但大多数我的用户永远不会看到这个。我不喜欢这个答案，但我不太喜欢更改表场景。
有人有什么想法或想法吗？
最后，这是一个数据库后端可以是oracle、sql server或access的产品（可能不是，但也许）
I've got a product I'm redesigning.
The old db design was ...
ID, Key, Value, all strings, where the value could be a number, text, or a date. Trying to do type specific searches on this data was impossible(or slow enough that it didn't matter). Key is a set of values that an object has, so say obj a has a birthdate of 1/12/1969, name of Dan, and phoneNumber of 555-555-5555, i nthe database this was stored as
a birthdate 1/12/1969
a name Dan
a phonenumber 555-555-5555
I want to redesign it so that the keys are actually fields so we would have this
a 1/12/1969 Dan 555-555-5555
The worry I have is that when the user goes in and adds a new Key, say cellphone, that adding the column could take a long time and/or be confusing to the user.
The users will most likely define the keys at the beginning, but there is a chance that a key gets added after years of using the product.
One thought I had was adding several columns of the different types we support to the table as empty and then renaming them as a user would add them, yes, I would still have the problem when I run out of these empty columns, but most of my users would never see this. I don't like this answer, but I like the alter table scenario somewhat less.
Anyone have any thoughts or ideas?
Finally, this is a product where the database backend can be oracle, sql server, or access(probably not, but maybe)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以结合这两种设计，花时间真正定义关系表中实际需要的 95%。然后有一个用于必要的可定制字段的 EAV 表。如果您确实做好了设计以包含通常需要的内容，那么大多数客户永远不会添加一个。
另一种替代方法是创建一个表，其中包含用户可以定义的一组可自定义字段，然后将它们限制为仅 6 个自定义字段。
第三种替代方法是让自定义字段位于与主表具有一对一关系的单独表中。这是通过左连接来连接的。当客户添加新字段时，您仍然需要更改此结构，但与添加到常规表相比，它对其他数据的干扰应该更少。
XML 数据类型或大型 varchar 字段（例如 SQL Server 中的 varchar (max)）可能是另一种可能性，特别是如果您只想显示自定义数据而不对其进行任何专门查询。
You can combine the two designs, take the time to really define the 95% that is actually needed in realtional tables. Then have an EAV table for the necessary customizable fields. Most customers will never add one if you have really done a good job of designing to include what will normally be needed.
Another alternative is to create a table with a set number of customizable fields that the user can define and then they will be limited to only 6 custom fields.
A third alternative is to have the custom field be in a separte table with a one to one relationship to the main table. This is joined to with a left join. You still have to change this structure when the customer adds a new field, but it should disrupt the other data less than if they add to the regular tables.
An XML data type, or large varchar field (such as varchar (max) in SQL Server) might be another possibility especially if you only want to display the custom data and not do any querying specifially on it.
您所描述的内容称为 实体属性值。对这样的事情进行建模很困难，您决定的任何解决方案都可能至少有一些缺点。你应该看看这个 stackoverflow 问题，它对这个主题有相当全面的讨论：
实体属性值数据库与严格关系模型电子商务
What you're describing is called Entity Attribute Value. Modeling something like this is difficult and any solution you decide on will probably have at least some disadvantages. You should take a look at this stackoverflow question that has a pretty comprehensive discussion on the topic:
Entity Attribute Value Database vs. strict Relational Model Ecommerce
键/值表的设计完全是垃圾，所以我很高兴你摆脱了它！
我建议不要在表中添加额外的列以供将来扩展。每个表的每个字段都应该有不同的解释，并且应该限制为仅保存该解释的合理值。
如果您想要用户设计的自定义字段，那么键/值系统可能就足够了（而且仅此而已），但要知道对此类用户设计的数据的查询和约束将更加困难和逊色。
The Key/Value table design is total junk, so I am glad you're getting rid of it!
I would advise against adding extra columns to the tables for future expansion. Every field of every table should have a distinct interpretation, and should be constrained to hold only sensible values for that interpretation.
If you want to have user-designed custom fields, then a key/value system may suffice for this (and only this), with the understanding that queries and constraints on such user-designed data will be more difficult and inferior.
您可以将键映射到 oracle 数据类型，并在检索值时根据数据类型使用特定函数。
例如
ID 键数据类型值
出生日期 1969 年 1 月 12 日
姓名字符串 Dan
电话号码字符串 555-555-5555，
因此每个用户信息将有 3 行，您可以根据需要添加更多。
在检索时，您可以根据 DATATYPE 值使用解码。
然而，该方法可以增加空间利用率。
you can map the keys to the oracle data type and use specific functions based on the data type while retrieving the values.
e.g.
ID KEY DATATYPE VALUE
a birthdate date 1/12/1969
a name string Dan
a phonenumber string 555-555-5555
so you will have 3 rows for each user info, you can add more as you need.
while retrieving u can use decode based on the DATATYPE value.
This method however may increase the space utilization.