允许用户即时向其数据库添加列的最佳方法是什么? [英] What is the best way to allow a user to add columns to their database on-the-fly?

问题描述

我们正在考虑设计一个用于管理联系信息的应用程序.

We're looking at designing an application for managing contact information.

客户的要求之一是允许他们的超级用户或管理员类型的用户通过UI向数据库添加列.客户没有直接访问数据库的权限(即，他们无法使用SSMS打开数据库并进行更改).

One of the customer's requests is to allow their powerusers or admin-type staff to add columns to the database through the UI. Customers do not have direct access to the database (i.e., they can't open it up with SSMS and make changes).

我看到以下控件:1)输入字段名称，以及2)输入数据类型，以及3)选择应在其中添加字段的表.用户单击按钮后，将使用新字段更新数据库.我将需要检查适当的权限，该字段是否已经存在等，等等.我们还需要将表单字段连接到这个新字段，例如报表设计器界面.

I see controls for 1)entering the name of field, and 2) entering the data type, and 3) selecting the table to which the field should be added. Once the user clicks a button, the database is updated with the new field. I'll need to check for proper permissions, does the field already exist, etc, etc. We'll also need to wire up the form fields to this new field, like a report designer interface.

1. 在哪里可以找到显示我想做的事的样本?
2. 这样做有什么弊端?除了对于开发人员而言，这样做似乎是一件可观的工作.
3. 您会建议这样做吗?如果没有，什么是更好的解决方案?

编辑-我们需要使用SQL Server.不能商量.

Edit - We are required to use SQL Server. Not negotiable.

推荐答案

是对它们向表中添加列的绝对要求，还是仅仅是他们能够指定要存储的其他字段?我强烈建议您考虑使用诸如Entity-Attribute-Value之类的方法，而不是允许最终用户(管理员算作最终用户)进行架构更改.

Is it an absolute requirement that they add columns to the table, or just that they be able to specify additional fields to store? I would strongly suggest you consider something like Entity-Attribute-Value rather than allowing the end-user (admins count as end-users) to make schema changes.

对于这样的事情，您将有一个表来定义您的自定义字段，然后是一个多对多关联表，以允许用户为联系人的自定义字段指定一个值.例如:

For something like this, you'd have a table to define your custom fields, then a many-to-many association table to allow the user to specify a value for a custom field for the contact. For instance:

    Contact
    ---------
 -> ContactId
|   FirstName
|   LastName
|   etc.
|
|                        ContactField
|                        --------------
|                        ContactFieldId <---
|                        FieldName          |
|                                           |
|   ContactFieldValue                       |
|   -------------------                     |
 -- ContactId                               |
    ContactFieldId -------------------------
    Value

实现的细节显然取决于您(例如，是否在 ContactFieldValue 中使用 ContactId + ContactFieldId 作为复合主键)，但这应该可以总体思路.

The specifics of implementation are obviously up to you (for instance, whether to use ContactId + ContactFieldId as a compound primary key in ContactFieldValue), but this should get the general idea across.

这篇关于允许用户即时向其数据库添加列的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！