数据库锁表:数据库中的数据库(表设计)(joinpod)-边看边学

Possible Duplicate:
Database design to create tables on the fly

我需要在数据库中创建数据库。这个问题与this有关。我将尝试详细解释我试图做的事情,并提供代码中的示例。基本上,我希望能够在数据库中创建动态表。例如,我将有一个网页,允许用户创建自己的表与列和数据。这里是我想出的数据库设计:

aColumn
aDataType
aRow
aTable
zBit
zDateTime
zMoney
zNumber
zText

以z开头的表是特定数据进入的数据，如 int 、 datetime 值等。aColumn 是属于特定表的列。aRow 标识 aTable 中的特定行。以下是数据库设计：

aTable: Id, name
aColumn: Id, Name, aTable, aDataType
aDataType: Id, Name
aRow: Id, aTable
zBit: Id, aRow, aColumn, Data(Bit)
zDateTime: Id, aRow, aColumn, Data (DateTime)
zMoney: Id, aRow, aColumn, Data (Money)
zNumber: Id, aRow, aColumn, Data (INT)
zText: Id, aRow, aColumn, Data (nvarchar(MAX))

以下是我用来启动和运行的一些示例数据：

aTable

Id          Name
1           Users

aColumns

Id          Name           aTable       aDataType
1           Name           1            2
2           UserId         1            1
3           Occupation     1            2

aDataType

Id          Name
1           Number
2           Text

aRow

Id          aTable
1           1
2           1

aNumber

Id          aRow           aColumn      Data
1           1              1            1245
2           2              2            56

aText

Id          aRow           aColumn      Data
1           1              1            Sara
2           2              1            Jake

所有其他 z * 表都是空白的

以下是创建动态表的查询：

select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zBit] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zDateTime] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zMoney] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zNumber] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]
UNION ALL
select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]

这里是这个查询的一个块：

select t.[Id] as [Table], c.Name as [Column], dt.Name as [DataType], r.[Id] as [Row], cast(v.Data as nvarchar(MAX)) as Data from [pod].[dbo].[aTable] t
INNER JOIN [pod].[dbo].[aColumn] c on t.Id = c.[aTable]
INNER JOIN [pod].[dbo].[aDataType] dt on c.[aDataType] = dt.Id
INNER JOIN [pod].[dbo].[aRow] r on t.[Id] = r.[aTable]
INNER JOIN [pod].[dbo].[zText] v on c.[Id] = v.aColumn and r.[Id] = v.[aRow]

正如你可以在这里看到一块数据（z * 表）由行和列标识。

Results

Table       Column         DataType     Row           Data
1           UserId         Number       1             1245          
1           UserId         Number       2             56
1           Name           Text         1             Sara
1           Name           Text         2             Jake

这里，是我想要的结果：（我不知道如何将这些行变成列，如果列是未知）

Row         UserId       Name
1           1245         Sara
2           56           Jake

大问题这个表是假设有 3 列记得吗？

aColumns

Id          Name           aTable       aDataType
1           Name           1            2
2           UserId         1            1
3           Occupation     1            2

所以我最终的预期结果是：

Row         UserId       Name         Occupation
1           1245         Sara         NULL
2           56           Jake         NULL

在结果中我也需要对列进行排序。这甚至是可能的。什么数据库支持这种功能。我对任何可以做到这一点的数据库都持开放态度。

然后，您可能需要查看数据的Entity Attribute Value model (E)设计。

基本可以有一个表的表名，和一些其他元数据的表。

然后，您可以为这些行中的每一行创建一个表，以获取列数据，如数据类型和名称。

然后，你有一个表，你把每列的值放在一个长表中。

这允许您动态创建表，或动态添加 / 删除行。

对于关系和 E 的比较，你可以看看这个问题：

Entity Attribute Value Database vs.strict Relational Model Ecommerce

如果你想有这个数据的关系视图，那么你需要创建触发器来帮助保持视图是最新的，这可能是一个很大的工作，以获得良好的工作。

另一种方法是使用 NoSQL 数据库（http://en..org/wiki/NoSQL），因为不必设置模式，因此您可以存储该行所需的列。

在这一点上，我会去 NoSQL 的方式，因为有许多数据库可以工作，而你需要做的重新发明是最小的。

对于问题的最后一部分，您正在询问如何对 E 模式执行cross-tabulation查询。一些数据库通过 SQL 标准的扩展支持这一点，其他数据库根本不支持它。为了可移植性，你必须在你的应用程序中做到这一点。PostgreSQL 为此提供了crosstab function in the tablefunc extension。

如果你沿着E路径走下去，你迟早会后悔的。它在某些有限的情况下很有用，但它不适合关系模型，会导致很多痛苦和问题，尤其是糟糕的性能。

改为考虑：

如果可能的话，重新设计，所以你不需要动态模式。在你的情况下可能不可能，因为你的明确要求是基于 Web 的数据库应用程序的用户可编辑模式，但在大多数情况下，这是正确的选择。

使用ALTER TABLE，CREATE TABLE等动态创建 / 删除模式。某些数据库在这方面比其他数据库要好得多。PostgreSQL 的事务性 DDL 可以提供很多帮助。需要注意避免这成为性能和维护的噩梦，但是如果您尝试使用动态结构建模关系数据库，这可能是最明智的选择。

为类似 E 的查询而优化的键 / 值存储；请参见Key/Value stores。请注意，这些系统中的许多系统不提供完整的 ACID 语义，并且可能具有有限的查询语言，因此您可以在应用程序中完成更多工作。

将 XML 或 JSON 存储在数据库中。您可以使用关系型数据库来做到这一点,但使用文档数据库可能会更好。与 K / V 商店相同的警告适用。如果您正在执行应用程序中的所有查询逻辑,并且您的数据大小不太大,则此方法可以正常工作。

使用数据库特定的功能，如 PostgreSQL 的hstore在需要时支持任意键 / 值存储，并使用不需要 k / v 的标准关系设计。如果你想要关系作为输出，它仍然是一个主要的 PITA，涉及低效的交叉表查询和连接。

Chris提出了一个很好的观点：您的整个设计非常可疑，请参见：The inner platform effect和TDWTF's take on it。说真的，不要去那里。

本站系公益性非盈利分享网址，本文来自用户投稿，不代表边看边学立场，如若转载，请注明出处

数据库锁表:数据库中的数据库(表设计)(joinpod)

发表评论

评论列表（52条）

数据库锁表:数据库中的数据库(表设计)(joinpod)

相关推荐

发表评论

评论列表（52条）