动态表列

Question

我有一个场景，在这个场景中，用户可以定义列，它可以是主表。该主表通过引用其主键来具有事务表，并且还具有一些公共列，其值对于每条记录是不同的。

例如，用户创建了名为X3的表，并定义了列X1、X2、X3等。Transaction表为Tran_XYZ，它包含对XYZ的引用以及列X1、X2、X3，其值在此处为override。

我需要在asp.net中实现这个动态专栏，请给我推荐最好的技术和方法。

Answer 1

嗯，关于您应该做什么，我不确定我有一个很好的答案，但我建议不要让您的应用程序使用DDL在数据库中动态创建实际的表。这并不是说永远不应该这样做，但我会说99%的时间这可能不是一个好主意。

事务性表也是动态创建的吗？该事务表中的所有记录是否都将与用户正在创建的主表相关，或者该事务表中的某些记录是否也将与其他用户创建的其他主表相关？

如果只有一个事务表需要引用多个用户定义的主表，那么我会考虑使用一个没有任何引用这些用户定义主表的列的核心事务表。然后，我将拥有另一个与该事务性表具有一对一或一对多关系的表，该事务性表确实引用了适当的主表。

如上所述，我会考虑不在数据库中创建实际的表来表示用户定义的主表。对于SQL Server 2005或更高版本，我会考虑采用如下方法：

编辑:修正了我的格式。

-- this table identifies the user-defined master tables
create table UserDefinedMasterType (
  MasterTypeID int not null,
  MasterTypeName varchar(50) not null

  -- primary key on MasterTypeID
)

-- this table stores the data for each user-defined master table
-- each record is uniquely identified by the MasterTypeID and MasterDataID
create table UserDefinedMasterData (
  MasterTypeID int not null,
  MasterDataID int not null,
  Data xml not null

  -- primary key on MasterTypeID and MasterDataID
  -- foreign key on MasterTypeID to UserDefinedMasterType.MasterTypeID
)

- this is the core transactional table that does not contain any references to user-defined master data
create table TransactionalData (
  TransactionID int not null primary key,
  -- other transactional columns,
)

-- this is a table related to the core transactional table that also contains references to the user-defined master table
create table TransactionalUserDefinedData (
  TransactionID int not null,
  MasterTypeID int not null,
  MasterDataID int not null,  
  Data xml,

  -- primary key on TransactionID and MasterTypeID
  -- foreign key on TransactionID to TransactionalData.TransactionID
  -- foreign key on MasterTypeID and MasterDataID to UserDefinedMasterData.MasterTypeID and UserDefinedMasterData.MasterDataID
)

因此，TransactionalUserDefinedData表与TransactionalData表是一对多关系，后者是核心事务表。TransactionalUserDefinedData将事务记录与0、1或多个用户定义的主表相关联。UserDefinedMasterData表和TransactionalUserDefinedData表上的XML列都允许您动态指定数据的“列”。

我将TransactionalUserDefinedData表中的数据列保留为可以为空，认为该列中的空值表示主表中的数据没有被覆盖，该表中的数据应该用于该记录。

如果您不希望像我的示例中那样允许事务记录与多个用户定义的主表相关，那么您可以将TransactionalUserDefinedData表上的主键更改为仅TransactionID列，这会将该表与TransactionalData列的关系从一对多更改为一对一，或者只将TransactionalUserDefinedData表中的列移动到TransactionalData表中。