数据库中的数据库(表设计) [英] Database within a database (table design)

问题描述

可能重复:
用于即时创建表的数据库设计

Possible Duplicate:
Database design to create tables on the fly

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

I have a need to create a database within a database. This question is related to this. I will attempt to explain what i am trying to do in detail and provide examples in code. basically, I want to ability to create dynamic tables in a database. For example, I will have a webpage that allows users to create their own table with columns and data. Here is the database design I came up with:

aColumn
aDataType
aRow
aTable
zBit
zDateTime
zMoney
zNumber
zText

以z开头的表是特定数据输入的数据，例如int，datetime值等.aColumn是属于特定表的列. aRow标识aTable中的特定行.这是数据库设计:

Tables that begin with a z are data that specific data go in such as ints, datetime values, etc. aColumn are columns that belong to a specific table. aRow identifies a particular row within the aTable. Here is the database design:

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))

这是我用来启动和运行的一些示例数据:

Here is some sample data I used to get it up and running:

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 *表均为空白

All other z* tables are blank

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

Here is the query to create a dynamic table:

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]

以下是该查询的其中一部分:

Here is one chunk of this query:

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 *个表)由一行和一列标识.当我运行此查询时，我得到以下信息:

As you can see here a piece of data (z* tables) is identified by a row and a column. When I run this query I get this:

结果

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

这是我想要的结果: (如果列未知，我不确定如何将这些行转换为列)

Here, are my desired results: (I am not sure how to turn these rows into columns if the columns are unknown)

Row         UserId       Name
1           1245         Sara
2           56           Jake

---

大问题 该表应该记住3列吗?

---

Big Problem This table is suppose to have 3 columns remember?

aColumns

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

---

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

---

So my final expected results are:

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

在结果中，我还需要对列进行排序.这有可能吗?哪些数据库支持这种功能.我对任何可以做到这一点的数据库敞开大门.

In the results I need to sort columns as well. Is this even possible. What databases support this kind of functionality. I am wide open to any database that can do this.

推荐答案

您可能希望查看基本上，您可以拥有一个带有表名的表，以及表上的其他一些元数据.

Basically you can have a table with table names, and some other meta-data on tables.

然后，您可以为每个行创建一个表以包含列数据，例如数据类型和名称.

Then, you can create a table for each of those rows to have the column data, such as datatype and name.

然后，您有了一个表，将每列的值放在一个长表中.

Then, you have a table where you put the values for each column, in a long table.

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

This allows you to dynamically create tables, or add/remove rows dynamically.

要比较关系型和EAV，您可以看一下这个问题:

For a comparison on relational and EAV you can look at this question:

实体属性值数据库与严格的关系模型电子商务

但是，如果您想获得此数据的关系视图，则需要创建触发器来帮助使视图保持最新状态，而要使其正常工作可能需要进行大量的工作.如果您不需要关系视图，那应该没事.

If you want to have a relational view of this data though, then you will need to create triggers to help keep views up-to-date, and this can be a great deal of work to get that to work well. If you don't need a relational view then you should be fine.

另一种方法是使用NoSQL数据库( http://en.wikipedia.org/Wiki/NoSQL )，因为不必设置架构，因此您只需存储该行所需的列即可.

Another way to do this is with a NoSQL database (http://en.wikipedia.org/wiki/NoSQL), as the schema doesn't have to be set, and so you can just store the columns you need for that row.

在这一点上，我将采用NoSQL的方式，因为有许多可以使用的数据库，并且您需要做的重新设计很少.

At this point I would go the NoSQL way as there are many databases that can work, and the reinventing you need to do is minimal.

这篇关于数据库中的数据库(表设计)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！