Suppose I have a products table, in which a few fields will be reserved and all other attributes are user generated nullable columns.
假设我有一个产品表，其中一些字段将被保留，而所有其他属性都是用户生成的可空列。
(reserved)
__
/ \
/ \
--------------------------------------
| id | name | color | height | width |
--------------------------------------
Like EAV, it will allow any number of properties, but the properties will be queryable as well. What are the potential downsides of this approach?
与EAV一样，它将允许任意数量的属性，但是属性也是可查询的。这种方法的潜在缺点是什么?
-
Can we rule out security issue if the only thing user controls in
ADD/DROP COLUMNstatements is field name (which would be always validated to prevent dropping reserved fields)?如果用户在ADD/DROP列语句中控制的惟一内容是字段名(总是对字段名进行验证以防止删除保留字段)，那么是否可以排除安全性问题?
-
How expensive
ADD/DROP COLUMNstatements may become when tables grow really large? Assuming we have rate limiting in place to avoid abuse of the system by user.当表变得非常大时，添加/删除列语句的开销会有多大?假设我们有速率限制以避免用户滥用系统。
-
How many (nullable, non-indexed) columns are too many for a single table, from performance perspective?
从性能角度看，单个表中有多少(空的、非索引的)列太多了?
4 个解决方案
#1
3
You'd be far better off with a second table with the key/value pairs.
您最好使用带有键/值对的第二个表。
And what makes you think the second table approach wouldn't be queryable?
是什么让你认为第二种表格方法是不可以查询的?
DDL statements cannot be in a transaction. It would probably depend on the database engine you're using, but I wouldn't be surprised if DDL would have to wait until every other transaction finished, and/or it would block all other transactions while waiting for the other transactions finish. In other words, the performance would suck.
DDL语句不能在事务中。它可能依赖于您正在使用的数据库引擎，但如果DDL必须等到所有其他事务完成，并且/或DDL在等待其他事务完成时阻塞所有其他事务，我不会感到奇怪。换句话说，表演将会很糟糕。
#2
0
Dynamically modifying a schema at runtime for a relational database is extremely expensive and can usually have some ill effects (not to the point of unleashing things and eating children, but close ;))
在运行时为关系数据库动态地修改模式是非常昂贵的，并且通常会有一些不良影响(不是为了释放东西和吃孩子，而是为了关闭;)
So I'd look at two choices.
我看两个选项。
-
Leave in generic field names of varying types, with a configuration database that maps what the user chose to use these extra fields for so you have proper column headings, etc. in custom reporting processes (I've seen this used in several ERP packages).
保留不同类型的通用字段名，使用配置数据库映射用户选择使用这些额外字段的内容，以便在自定义报告过程中拥有适当的列标题等(我在几个ERP包中见过这种情况)。
-
Consider using a non-relational database that allows storing disparate objects (generally in JSON, etc.)
考虑使用允许存储不同对象(通常是JSON等)的非关系数据库。
#3
0
Instead of adding columns to your existing table, how about creating a second table that contains key-value pairs of attributes with a foreign key to the products table? This will allow for any number and variety of attributes for products, and is easily queryable by joining the tables on the primary key-foreign key.
与其向现有表添加列，不如创建第二个表，该表包含具有product表外键的属性的键-值对?这将允许为产品提供任意数量和各种属性，并且可以通过连接主键-外键上的表来轻松查询。
#4
0
In production modification of database schemas is a headache. User modification of database schemas is the kind of headache you get when someone runs you over in a truck, then backs up and hits you again.
在数据库模式的生产修改中，令人头痛。数据库模式的用户修改是一种令人头痛的事情，当有人在卡车上碾过你，然后后退并再次撞到你。
Information added to the database by users during normal business processing (and this includes names of new attributes) is data, not schema information, and should be treated as such.
用户在正常业务处理期间向数据库中添加的信息(包括新属性的名称)是数据，而不是模式信息，应该这样处理。
#1
3
You'd be far better off with a second table with the key/value pairs.
您最好使用带有键/值对的第二个表。
And what makes you think the second table approach wouldn't be queryable?
是什么让你认为第二种表格方法是不可以查询的?
DDL statements cannot be in a transaction. It would probably depend on the database engine you're using, but I wouldn't be surprised if DDL would have to wait until every other transaction finished, and/or it would block all other transactions while waiting for the other transactions finish. In other words, the performance would suck.
DDL语句不能在事务中。它可能依赖于您正在使用的数据库引擎，但如果DDL必须等到所有其他事务完成，并且/或DDL在等待其他事务完成时阻塞所有其他事务，我不会感到奇怪。换句话说，表演将会很糟糕。
#2
0
Dynamically modifying a schema at runtime for a relational database is extremely expensive and can usually have some ill effects (not to the point of unleashing things and eating children, but close ;))
在运行时为关系数据库动态地修改模式是非常昂贵的，并且通常会有一些不良影响(不是为了释放东西和吃孩子，而是为了关闭;)
So I'd look at two choices.
我看两个选项。
-
Leave in generic field names of varying types, with a configuration database that maps what the user chose to use these extra fields for so you have proper column headings, etc. in custom reporting processes (I've seen this used in several ERP packages).
保留不同类型的通用字段名，使用配置数据库映射用户选择使用这些额外字段的内容，以便在自定义报告过程中拥有适当的列标题等(我在几个ERP包中见过这种情况)。
-
Consider using a non-relational database that allows storing disparate objects (generally in JSON, etc.)
考虑使用允许存储不同对象(通常是JSON等)的非关系数据库。
#3
0
Instead of adding columns to your existing table, how about creating a second table that contains key-value pairs of attributes with a foreign key to the products table? This will allow for any number and variety of attributes for products, and is easily queryable by joining the tables on the primary key-foreign key.
与其向现有表添加列，不如创建第二个表，该表包含具有product表外键的属性的键-值对?这将允许为产品提供任意数量和各种属性，并且可以通过连接主键-外键上的表来轻松查询。
#4
0
In production modification of database schemas is a headache. User modification of database schemas is the kind of headache you get when someone runs you over in a truck, then backs up and hits you again.
在数据库模式的生产修改中，令人头痛。数据库模式的用户修改是一种令人头痛的事情，当有人在卡车上碾过你，然后后退并再次撞到你。
Information added to the database by users during normal business processing (and this includes names of new attributes) is data, not schema information, and should be treated as such.
用户在正常业务处理期间向数据库中添加的信息(包括新属性的名称)是数据，而不是模式信息，应该这样处理。