CODEWITHSUNDEEP
sqldatabasesql-server-2008testing
HoboDan
HoboDan

Reputation: 13

SQL Database Design for Test Data

So, I am trying to learn how to set up good, and usable databases. I have ran into a problem involving storing large amounts of data correctly. The database I am using is MSSQL 2008. For example:

We test about 50,000 devices a week. Each one of these devices have a lot of data associated with them. Overall, we are just looking at the summary of data calculated from the raw data. The summary is easy to handle, its just the raw data I'm trying to enter into a database for future use in case someone wants more details.

For the summary, I have a database full of tables for each set of 50,000 devices. But, each device there is data similar to this:

("DevID") I,V,P I,V,P I,V,P ...
("DevID") WL,P WL,P WL,P ...

Totaling to 126 (~882 chars) data points for the first line and 12000 (~102,000 chars) data points for the second line. What would be the best way to store this information? Create a table for each and every device (this seems unruly)? Is there a data type that can handle this much info? I am just not sure.

Thanks!

EDIT: Updated ~char count and second line data points.

Upvotes: 1

Views: 365

Answers (2)

Parris Varney
Parris Varney

Reputation: 11478

You could just normalize everything into one table

CREATE TABLE device
(  id         BIGINT AUTO_INCREMENT PRIMARY KEY
,  DevID      INT
,  DataPoint  VARCHAR
,  INDEX(DevID))

Psudocode obviously, since I don't know your exact requirements.

Upvotes: 1

nvogel
nvogel

Reputation: 25526

Does this data represent a series of readings over time? Time-series data tends to be highly repetetive. So a common strategy is to compress it in ways that avoid storing every single value. For example use run-length encoding, or associate time intervals with each value instead of single points.

Upvotes: 1

Related Questions