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))
以下是我用来启动和运行的一些示例数据:
aTableId 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 * 表)由行和列标识。
ResultsTable 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 列记得吗?
aColumnsId 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。说真的,不要去那里。
本站系公益性非盈利分享网址,本文来自用户投稿,不代表边看边学立场,如若转载,请注明出处
评论列表(54条)