将科学数据存储在关系数据库中 [英] Storing Scientific Data in a Relational Database

问题描述

我想在关系数据库（MySQL或SQLite）中存储层次化的二维科学数据集。每个数据集包含具有任意数量列的数值数据表。此外，每个数据集可以具有与其表的给定行相关联的相同类型的一个或多个子。每个数据集通常具有1到100列之间和1到1.000.000行之间。数据库应该能够处理许多数据集（> 1000），并且数据的读/写应该是相当快的。

什么是最好的DB模式存储这种数据？有一个主表有个别数据集的名称，ID和关系以及每个数据集包含数值的一个表是合理的吗？

解决方案

具有单个数据集的名称，ID和关系的主表格以及每个数据集一个表是合理的包含数值？

这是我该怎么做。

我不确定任意列的工作是如何工作的，因为数据通常不会这样工作。无论如何，它听起来像存储为行，col，val可能工作很好。

老实说，如果你不需要搜索它（最大，最小等等），最好使用某种平面文件。 / p>

另一个可能有趣的设置是使用SQLite，每个数据集有一个单独的数据库文件，加上一个主数据库。

无论你选择什么，它的工作效果真的取决于你将如何处理数据。

I want to store hierarchical, two-dimensional scientific datasets in a relational database (MySQL or SQLite). Each dataset contains a table of numerical data with an arbitrary number of columns. In addition, each dataset can have one or more children of the same type associated with a given row of its table. Each dataset typically has between 1 and 100 columns and between 1 and 1.000.000 rows. The database should be able to handle many datasets (>1000) and reading/writing of data should be reasonably fast.

What would the best DB schema to store such kind of data? Is it reasonable to have a "master" table with the names, IDs and relations of individual datasets and in addition one table per dataset which contains the numerical values?

解决方案

Is it reasonable to have a "master" table with the names, IDs and relations of individual datasets and in addition one table per dataset which contains the numerical values?

That's how I'd do it.

I'm not exactly sure how the 'arbitrary columns' thing is working, because data usually doesn't work like that. Regardless, it sounds like storing it as row,col,val might work nicely.

Honestly though, if you don't need to search through it (max, min, etc.), it might be better to use some kind of flat file.

An alternative setup that might be interesting is using SQLite, with a separate database file for each dataset, plus one master one.

Whatever you pick, how well it will work really depends on what you're going to do with the data.

这篇关于将科学数据存储在关系数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！