blocks|key|4460939|text|如果这是由您集中托管的，我建议不要允许用户输入的数据更改数据库的模式(即驱动新表的创建)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4460940|相反，您可能希望研究在SQL中使用XML字段来存储数据的变量字段名，或者更通用的表结构...如果我们谈论的不是疯狂的数据量，这种技术非常有效……|4460941|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If this is centrally hosted by you, I would suggest NOT allowing user-input data to change the schema of the database (i.e. drive the creation of new tables).

Rather you may want to look into using XML fields in SQL to store variable field names of data, or a more generic table structure... this technique works pretty well if we're not talking crazy amounts of data...

blocks|key|4460975|text|有没有可能你在横向看待你的解决方案？听起来你需要一个映射表(有点像你的#1)。你有一个表，比如说“对象”，一个叫做"properties“的表保存了你所说的列，还有一个表保存了这些值，所以它只有object_id，property_id，value。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4460976|用一种比我说的更聪明的方式，来看看Entity-attribute-value模型。|offset|length|4460977|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Entity-attribute-value_model^0|0|H|M|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Is it possible you're looking at your solution sideways? It sounds like you need a mapping table (sort of like your #1). You have a table, say "objects" for example, a table called "properties" which holds what you're calling columns and then a table that holds the values, so it just has object_id, property_id, value. 

To put in a smarter way than I said it, take a look at the <a href="http://en.wikipedia.org/wiki/Entity-attribute-value_model" rel="nofollow noreferrer">Entity-attribute-value</a> model.

blocks|key|3614048|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3614049|“要构建的系统将允许用户向系统中的表中添加新列...”|blockquote|3614050|3614051|3614052|真的吗?这就是用户故事吗？对我来说，听起来你已经对解决方案下定决心了。|3614053|允许用户扩展模式是不是一个好主意，这完全取决于上下文。在管理员式的、有限的使用方式下，我会遇到一些小问题。但是用MySpace类型的方式来说，这是一个非常糟糕的想法。我怀疑你的情况介于这两个极端之间。|3614054|扩展模式将导致更高效的查询-因为您可以添加索引等-但它确实向您的用户公开了一些关系规则。此外，扩展将(可能)锁定整个表，并且需要处理并发编辑。|3614055|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|N|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|O|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|P|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|Q|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|R|7|@]|8|@]|9|$]]|$1|H|3|I|4|5|6|S|7|@]|8|@]|9|$]]|$1|J|3|K|4|5|6|T|7|@]|8|@]|9|$]]|$1|L|3|-4|4|5|6|U|7|@]|8|@]|9|$]]]|M|$]]

<blockquote>
 "The system to be built will allow user to add new columns to a table in the system..."
</blockquote>

Really - that's the user story? Sounds like you've already made up your mind on the solution, to me.

Whether it's a good idea or not to allow a user to extend schemas is pretty context dependent. I'd have little problem in an admin-like, limited use way. But it'd be a horribly bad idea in a MySpace type way. I suspect your situation lies somewhere between those 2 extremes.

Extending the schema would lead to greatly more efficient queries - as you could add indexes and such - but it does expose some relational rules on your users. Also, the extension would (probably) lock the entire table and concurrent edits would need to be dealt with.

blocks|key|396007|text|任何好的建议都需要更好地理解你的需求，但这里有一些关于你提到的选项的评论，以及一些额外的想法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|396008|1)+实体-属性-值(EAV)设计：这是您描述的选项，其中您有一个表，其中包含ColumnName、类型和值的列。此选项的优点是能够轻松容纳无限的新列，但我发现当需要检索有意义的数据时，这是非常痛苦的。例如，假设您在此EAV表中有{Color，varchar}{Red，Green，Blue}和{Size，varchar}{Small，Medium，Large}的行。如果你想找到所有的绿色小东西，你需要类似这样的东西(当然是未经测试的SQL+)：|offset|length|style|BOLD|396009|SELECT+*+
FROM+ITEMS+
WHERE+ITEMID+IN+(SELECT+ITEMID+
+++++++++++++++++FROM+ITEM_ATTRIBUTES+ATT+INNER+JOIN+ITEM_VALUES+VLS
+++++++++++++++++++++ON+ATT.AttributeID+=+VLS.AttributeID
+++++++++++++++++WHERE+ATT.ColumnName+=+'Color'+AND+VLS.Value+=+'Green')
++AND+ITEMID+IN+(SELECT+ITEMID+
+++++++++++++++++FROM+ITEM_ATTRIBUTES+ATT+INNER+JOIN+ITEM_VALUES+VLS
+++++++++++++++++++++ON+ATT.AttributeID+=+VLS.AttributeID
+++++++++++++++++WHERE+ATT.ColumnName+=+'Size'+AND+VLS.Value+=+'Small')|code-block|syntax|javascript|396010|将其与items表上的实际列的颜色和大小进行对比：|396011|SELECT+*
FROM+ITEMS
WHERE+COLOR+=+'Green'+AND+SIZE+=+'Small'|396012|此外，如果这对这个应用程序很重要，你将很难维护数据完整性(而且几乎总是重要的，即使你被告知不是这样的)。在上面的示例中，如果"Color“应该限制为Blue、Green和Red，则需要实现额外的逻辑。此外，如果某些颜色只有特定的尺寸(例如，蓝色仅在中小型产品中可用)，您将需要实现更多的逻辑。|396013|2)++user+-Defined+Columns：只允许用户向表中添加额外的列的优点是使数据检索更简单，但所有数据完整性问题仍然存在。此外，您的应用程序通常需要额外的逻辑来处理未知的列。|396014|3)+预先存在的自定义列：我使用过一些应用程序，比如CRM，它们为用户定义提供了十几个甚至更多的列。基本上，设计者将"Text1“、"Text2”、"Text3“、"Number1”、"Number2“等列放入列中，然后用户为这些列提供标题和描述信息，这就是应用程序用于显示的目的。此模型具有易于数据检索的优势，以及预定义的DB模式，这应该简化应用程序逻辑。然而，数据完整性问题仍然存在。另一个明显的缺点是，您将耗尽预定义的列，而这正是您在使用这种类型的解决方案时通常试图避免的。|396015|与大多数设计问题一样，每个解决方案都有权衡之处。我的经验是，虽然许多用户/客户说他们想要这样的解决方案，但实际上他们只是想确保自己不会被一个无法满足自己需求的应用程序所困。我发现，实际上很少有地方需要这样的设计。我几乎总是可以创建一个满足客户端扩展需求的设计，而不需要让他们扮演数据库设计师的角色。|396016|entityMap^0|0|3|E|0|0|0|0|0|3|M|0|3|9|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|18|8|@$D|19|E|1A|F|G]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1B|8|@$D|1C|E|1D|F|G]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|Z|$]]

Any good recommendations would require a much better understanding of your requirements, but here are some comments on the options you mentioned, as well as some additional thoughts.

1) Entity-Attribute-Value (EAV) Design: This is the option you describe where you have a table that has columns for ColumnName, Type and Value. This option has the advantage of being able to accomodate unlimited new columns easily, but I have found it to be painful when the time comes to retrieve meaningful data back. For example, say you have rows in this EAV table for {Color, varchar}{Red, Green, Blue}, and {Size, varchar}{Small, Medium, Large}. If you want to find all the small green items, you need something like this (untested SQL of course):

<pre><code>SELECT * 
FROM ITEMS 
WHERE ITEMID IN (SELECT ITEMID 
 FROM ITEM_ATTRIBUTES ATT INNER JOIN ITEM_VALUES VLS
 ON ATT.AttributeID = VLS.AttributeID
 WHERE ATT.ColumnName = 'Color' AND VLS.Value = 'Green')
 AND ITEMID IN (SELECT ITEMID 
 FROM ITEM_ATTRIBUTES ATT INNER JOIN ITEM_VALUES VLS
 ON ATT.AttributeID = VLS.AttributeID
 WHERE ATT.ColumnName = 'Size' AND VLS.Value = 'Small')
</code></pre>

Contrast this with having actual columns on the items table for color and size:

<pre><code>SELECT *
FROM ITEMS
WHERE COLOR = 'Green' AND SIZE = 'Small'
</code></pre>

In addition, you will have a difficult time maintaining data integrity, if that is important for this app (and it is almost always important, even when you are told otherwise). In the example above, you will need to implement extra logic if "Color" should be limited to Blue, Green, and Red. Also, you will need to implement even more logic if certain colors only come in certain sizes (example - blue items are only available in small and medium)

2) User-Defined Columns: Just giving the user the ability to add additional columns to the table has the advantage of making data retrieval simpler, but all the data integrity issues remain. Also, your app usually requires extra logic to deal with the unknown columns.

3) Pre-Existing Custom Columns: I have worked with a few apps, such as CRMs, that provide a dozen or more columns already in place for user definition. Basically, the designers put in columns like "Text1","Text2","Text3","Number1","Number2", etc. The users then provide header and description information for these columns, and that is what the app uses for display purposes. This model has the advantage of easy data retrieval, as well as a pre-defined DB schema which should simplify app logic. Data integrity issues remain, however. The other obvious downside is that you will run out of pre-defined columns, which is what you are usually trying to avoid with this type of solution.

As with most design issues, there are tradeoffs to each solution. My experience has been that while many users/clients say they want solutions like these, in reality they are simply trying to ensure they don't get trapped with an app that can't grow with their needs. I have found that there are actually very few places where a design like this is needed. I can almost always create a design that addresses the expansion desires of the client without putting them into the role of database designer.

I am facing this question in a new little project:
The system to be built will allow user to add new columns to a table in the system, and then the user will be able to maintain the data, I think there is two ways to implement this:
1) create a few tables including "columns" table with "columnName" "columnValue" "datatype" etc to store the column definition, aonther table "XXCoumn" to store the value of the column (entered by user), and user a store procedure to query/update column data.
2) create the column in the table schema when user enter a new column, then the maintain of the table data is just as normal

which way do you guys reckon? or any new suggestion?

Some additional info: the data volumn is small, and I need to create reports.

Add new columns in asp .net application

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在一个新的小项目中面临这个问题:要建立的系统将允许用户添加新的列到系统中的表中，然后用户将能够维护数据，我认为有两种方法来实现这一点: 1)创建几个表，包括“列”表与"columnName“"columnValue”“数据类型”等来存储列定义，另一个表"XXCoumn“来存储列的值(由用户输入)，并使用一个存储过程来...

问在asp .net应用程序中添加新列
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在asp .net应用程序中添加新列EN