CODEWITHSUNDEEP
sqlsql-serverdatabase-designazure-sql-database
Ed Sinek
Ed Sinek

Reputation: 4987

SQL Design: need ability to add custom "columns" to a table using a fixed schema

Using: SQL Server 2008, Entity Framework, 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).

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.

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.

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

Upvotes: 1

Views: 878

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 1

gbvb
gbvb

Reputation: 886

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}

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

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.

Upvotes: 2

Related Questions