SQL设计：需要能够使用固定模式向表中添加自定义“列”

Using: SQL Server 2008, Entity Framework, WCF 4 REST

使用:SQL Server 2008,实体框架,WCF 4 REST

I have a table for holding the measurement data generated by monitoring system (aka the app). There are currently about 10 different well-known pieces of data being monitored within the app - and each corresponds to a column in the table. Each customer can "customize" each of their apps to capture from 1 to 10 pieces of data - they only need to capture those pieces of info they are interested in analyzing. Everything's working great (and performance is good) with this straight-forward fixed schema. This schema is designed to be multi-tenant, so multiple applications across multiple customers in multiple locations could be pumping data into the same DB - millions and millions of rows of measurement data (I wouldn't be surprised if we go to Azure before too long).

我有一个表用于保存由监控系统(也就是应用程序)生成的测量数据。目前在应用程序中监控大约10种不同的众所周知的数据 - 每个数据对应于表中的一列。每个客户都可以“自定义”他们的每个应用程序,以捕获1到10个数据 - 他们只需要捕获他们感兴趣分析的那些信息。使用这种直接的固定模式,一切都运行良好(性能良好)。这种模式设计为多租户,因此多个位置的多个客户的多个应用程序可以将数据泵入同一个数据库 - 数百万行数据行(如果我们之前也要访问Azure,我也不会感到惊讶)长)。

I have now been told that the measurement application will soon be able to monitor additional "things". This new list (so far I'm told is at around 150 items) could wind up being about 1000 items being measured. Add on top of that, the user could specify their own criteria for items to monitor/measure (i.e. custom measurements equating to custom columns.) The good news is that all the measurement data will be integers.

我现在被告知测量应用程序很快就能监视其他“事物”。这个新的清单(到目前为止,我被告知大约150件物品)最终可能会被测量约1000件物品。除此之外,用户可以为要监控/测量的项目指定自己的标准(即自定义测量等于自定义列。)好消息是所有测量数据都是整数。

Now the fun - how do I design the schema for this situation? I would really like to keep the schema fixed. I would also like to keep it as performant as possible given the high volumes of data.

现在好玩 - 如何为这种情况设计架构?我真的想保持架构的固定。考虑到大量数据,我还希望尽可能保持高性能。

Any help is appreciated.

任何帮助表示赞赏。

Current Schema:

CREATE TABLE MeasurementData (
    DataId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ApplicationId int NOT NULL,    -- FK to Application table
    DateCollected datetime NOT NULL,
    Length int NULL,
    Width int NULL,
    Height int NULL,
    Color int NULL,
    Shape int NULL,
    Mass int NULL
)
CREATE TABLE Application (
    ApplicationId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CompanyId int NOT NULL,    -- FK to Company table
    SerialNumber nvarchar(50) NOT NULL
)
CREATE TABLE Company (
    CompanyId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CompanyName nvarchar(50) NOT NULL
)

And then we have the user table, the roles table, etc, where there's a 1-n relationship btw company and user.

然后我们有用户表,角色表等,其中有一个公司和用户的1-n关系。

FYI, the web app will then present the data with tables, graphs, etc (communicating via the REST web service layer)

仅供参考,网络应用程序将使用表格,图表等显示数据(通过REST Web服务层进行通信)

2 个解决方案

#1

Can you add two more tables? One with the types of measurements and the other with a mapping from the type to the measurement itself?

你还可以再增加两张桌子吗?一个是测量类型,另一个是从类型到测量本身的映射?

Basically A table with {DataId, DataMeasurementTypeId, DataValue} and {DataMeasurementTypeId, DataMeasurementType}

基本上是一个包含{DataId,DataMeasurementTypeId,DataValue}和{DataMeasurementTypeId,DataMeasurementType}的表

That should allow you to provide stored procedures to retrieve all Datameasurements in a table.

这应该允许您提供存储过程来检索表中的所有Datameasurements。

The better optiom might be to solve it with a Name,Value table and have the business object layer take care of constructing the right content.. That would fit (and likely perform) better with BigTable approach of Google than RDBMS though.

更好的optiom可能是使用Name,Value表来解决它,并让业务对象层负责构建正确的内容。但是,这比Google的BigTable方法更适合(并且可能更好),而不是RDBMS。

#2

Take a look at these SO examples: one, two, three.

看看这些SO例子:一,二,三。

#1