何时为空值“安全”在列中？ [英] When are nulls "safe" in a column?

问题描述

设计数据库是否有一般的经验法则允许列为null和3nf标准化？我有一个表，其中大部分都包含空值（85％），但表格大小不超过10K记录（不是很大）。它主要用于记录和记录保存，所以大多数事务将被插入和选择，没有更新。我试图考虑性能和简化设计。在这种情况下，是否有非正规化或正规化？不同的RDBMS行为不同？

解决方案

有三个不同的原因可以避免NULL。按照重要性（在我看来，当然），它们是：

1. 你想正确建模你的数据。 p>




2. 您希望提供一个数据库，方便您制作正确的应用程序。




3. 想要节省磁盘空间。




4. 您担心的是性能。

好吧，有四个分开的原因！

在四个中，我非常关心＃1 ，非常关心＃2（特别是因为我一直都是程序员），我真的不太在意＃3，因为磁盘空间是便宜的，也因为＃1和＃2摆脱了大多数NULL数据库为我对于＃4，我不想牺牲正确的表现 - 如果我不能确信它是正确的，我得到的答案无论多么快。如果我在一个精心建模的SQL数据库中无法达到我的绩效目标，那么SQL数据库可能是该作业的错误工具。

所以最重要的问题是我会做单一表格方法是否正确建模您想存储的数据？同样重要的是单表方法会使数据库写入错误的代码变得容易吗？我想强迫自己（或跟随我的任何程序员）将将数据放在正确的位置。看看你的数据并回答这些问题，它应该帮助你决定正确的结构。

Is there a general rule of thumb for designing databases to allow the columns as nulls vs 3nf normalization? I have a table with a column that will mostly consist of nulls (85%) but the table size is not over 10K records (not very big). It's mostly for logging and record keeping so most transactions will be inserts and selects, no updates. I'm trying to consider both the performance as well as simplified design. Will there be a great benefit with either denormalized or normalized in this case? Do different RDBMSs behave differently?

解决方案

There are three separate reasons to avoid NULLs. In order of importance (in my opinion, of course) they are:

1. You want to correctly model your data.

2. You want to provide a database that makes it easy to produce correct applications.

3. You want to save disk space.

4. You're concerned about performance.

Okay, there are four separate reasons!

Of the four, I'm very concerned about #1, pretty concerned about #2 (especially since I'm always the programmer, too) and I really don't care much about #3 both because disk space is cheap and also because #1 and #2 get rid of most NULLs in any database for me. As for #4, I never want to sacrifice correctness for performance — it doesn't matter how fast I get the answer if I can't be confident that it's correct. If I can't meet my performance goals in a well-modeled SQL database then an SQL database might be the wrong tool for the job.

So the most important question to me would be "does a single-table approach correctly model the data you want to store?" Also important is "will the single table approach make it too easy to write incorrect code against the database?" I want to force myself (or any programmer who comes after me) to have to put data in the correct places. Look at your data and answer those questions, and it should help you decide on the correct structure.

这篇关于何时为空值“安全”在列中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！