淹没在空海中 [英] Drowning in a Sea of Nulls

问题描述

我继承的应用程序跟踪材料样品上执行的实验室测试结果。数据存储在单个表（tblSampleData）中，主键为SampleID，235列表示潜在的测试结果。问题是每个样本只执行几个测试，因此每行包含超过200个空值。实际上，有一个第二个类似的表（tblSampleData2），另外有215个主要为null的列和一个主键的SampleID。两个表具有一对一关系，并且大多数SampleID在两个表中都有一些数据。 然而，对于每个SampleID，都容易有400个null列！

An application I inherited tracks lab test results performed on material samples. Data is stored in a single table (tblSampleData) with a primary key of SampleID and 235 columns representing potential test results. The problem is that only a few tests are performed per sample, so each row contains over 200 nulls. Actually, there is a second similar table (tblSampleData2) with another 215 primarily null columns and a primary key of SampleID. The two tables have a one-to-one relationship and most SampleIDs have some data in both tables. For every SampleID, however, there are easily 400 null columns!

这个数据库设计不好吗？如果是这样，哪个正常的形式规则被打破了？如何查询此表以确定哪些列通常与数据一起填充？我的目标是说，45个表与10列和较少的空值。我如何做到这一点？如何避免破坏现有的应用程序？

Is this bad database design? If so, which normal form rule is broken? How can I query this table to identify which groups of columns are typically filled together with data? My goal would be to have, say 45 tables with 10 columns and fewer null values. How can I do this? How do I avoid breaking existing applications?

表至今有约20万个样本记录。用户要求我为更多的测试添加更多的列，但我宁愿构建一个新的表。这是明智的吗？

The tables have about 200,000 sample records so far. Users are asking me to add more columns for more tests, but I'd rather build a new table. Is this wise?

推荐答案

我不知道设计真的那么糟糕。实际上，NULL值应该比较便宜。在SQL Server中，每行的内部位字段（或字段）指示哪些列值为NULL。

I'm not sure the design is really that bad. NULL values should actually be relatively cheap to store. In SQL Server, there is an internal bit field (or fields) for each row that indicates which column values are NULL.

如果应用程序的性能不需要要改进，并且由于更改表模式而重构的成本效益不是积极的，为什么要改变它？

If the performance of the application doesn't need to be improved and the cost-benefit of refactoring due to changing the table schema isn't positive, why change it?

这篇关于淹没在空海中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！