数据库设计 - 空字段 [英] Database design - empty fields

问题描述

我正在与我的开发团队辩论一个问题。他们认为空场是坏消息。例如，如果我们有一个客户详细信息表存储来自不同国家的客户的数据，并且每个国家的地址配置略有不同，再加上1-2个额外的字段，例如法国客户详细信息也可能存储入门代码的详细信息，以及楼层/等级加标题字段（madamme等）。南非将有一个安全号码。等等。

鉴于我们在谈论轻微的差异，我的想法是将所有字段放入表格中，并使用每个表单上需要的内容。

我的同事认为我们应该有一个单独的表与额外的数据。例如。 customer_info_fr。但是，这个接缝完全打破了组合表的目的。

参数是空的字段/列是坏的 - 但是我很努力地找到正确的数据库设计原则，或反对这个论点和首选解决方案。 / p>

另一个选项是一个单独的迷你EAV表，其中存储有parent_id，key，val字段的额外数据。或者将额外的数据序列化到customer_data主表中的extra_data列。

我觉得我很困惑，因为我正在讨论的内容没有被3NF覆盖，这是我的通常将用作参考如何构建数据。

所以我的问题具体： -

如果每个记录的数据有轻微差异（1-2

解决方案

空值总是增加数据模型的复杂性，因为行为SQL中的null很少与您打算用它建模的数学，逻辑或现实相匹配。换句话说，一些查询返回不正确的结果，然后您需要补充额外的逻辑。

所有信息都可以准确无误地表示。由于null增加了复杂性，所以在没有他们的情况下开始你的数据模型是合理的设计实践，然后只添加一个null，在那里你会发现一些特殊的原因，或者某些数据库特征或限制强制在你身上空。

I am currently debating an issue with my dev team. They believes that empty fields are bad news. For instance, if we have a customer details table that stores data for customers from different countries, and each country has a slightly different address configuration - plus 1-2 extra fields, e.g. French customer details may also store details for entry code, and floor/level plus title fields (madamme, etc.). South Africa would have a security number. And so on.

Given that we're talking about minor variances my idea is to put all of the fields into the table and use what is needed on each form.

My colleague believes we should have a separate table with extra data. E.g. customer_info_fr. But this seams to totally defeat the purpose of a combined table in the first place.

The argument is that empty fields / columns is bad - but I'm struggling to find justification in terms of database design principles for or against this argument and preferred solutions.

Another option is a separate mini EAV table that stores extra data with parent_id, key, val fields. Or to serialise extra data into an extra_data column in the main customer_data table.

I think I am confused because what I'm discussing is not covered by 3NF which is what I would typically use as a reference for how to structure data.

So my question specifically: -

If you have slight variances in data for each record (1-2 different fields for instance) what is the best way to proceed?

解决方案

Nulls invariably add complexity to a data model because the behaviour of null in SQL rarely matches the maths, logic or reality that you intended to model with it. In other words, some queries return incorrect results, which you then need to compensate for with additional logic.

All information can be represented accurately without nulls. Since nulls add complexity it is sound design practice to begin your data model without them and then only add a null where you find some special reason to do so or where some database feature or limitation forces a null upon you.

这篇关于数据库设计 - 空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！