有关可空外键和规范化的数据库设计问题 [英] DB Design Question about Nullable Foreign Keys and Normalization

问题描述

我希望就哪种数据库模式最适合我的情况达成共识，以便将Widget的类型"信息存储在表中. 窗口小部件只能具有一种类型，但是该类型可以是预设类型"或自定义类型".我显然创建了预设类型，用户将创建自定义类型.

I'm hoping to get a consensus about what db schema is best for my situation to store 'type' information for Widgets in a table. A Widget can have only one type but that type can be either a Preset-Type OR a Custom-Type. I obviously create the preset types and a user would create a custom type.

我将在服务器上使用MySQL和INNODB.我还将使用SQLite在应用程序上存储相同的信息.但是我们这里只讨论服务器.我是一名应用程序程序员，而不是数据库管理员，但希望首次使该数据库适合该项目并在合理的范围内进行标准化.

I'll be using MySQL and INNODB on a server. I'll also be using SQLite to store the same info on an App. But we'll just talk about the server here. I'm an app programmer, not a DB admin, but want to get the DBs right for this project the first time and normalized within reason.

在我是否应该对外键使用null的搜索中，我遇到了以下数据库答案，这些人的数据库经验比我多得多.

In my searching on whether or not I should use nulls for foreign keys I've come across the following answers from people who have much more DB experience than I do.

• 当然，在外键和其他地方都可以使用Null."
• 完全可以接受外键中的NULL."
• 外键是NULL不可缺少的一个区域."
• 几乎不应该使用null，尤其是在外键中."
• 在任何地方都不能使用null."
• 具有很多NULL值的列通常表示需要(进一步)规范化."

我需要知道在模型#2的特定情况下使用Null是否是错误的做法，以及哪种模型更可取，以及为什么.或可能建议一个更好的模型.感谢您的输入.

I need to know if it's bad practice to use Nulls in the particular case of Model #2 and which model is preferable and why. Or possibly suggest a better model. Thanks for any input.

对于预设和自定义类型都有一个类型"表.为此，我可以使用预设类型预先填充类型"表，并为以后添加的将来的预设类型保留大约1500个保留空间.

Have one 'types' table for both Preset and Custom Types. I do this by pre-populating the 'types' table with preset types and leaving around 1500 reserved spaces for future Preset-types that I could later add.

优点:简单，没有多余的表，没有联接，可能是最快的选择，并且从长远来看可能减少数据库空间(4字节type_id).小部件表type_id FK永远不会为NULL.

Pros: Easy, no extra tables, no joins, probably the fastest option, and prob less db space in the long run (4 byte type_id). And widgets table type_id FK will never be NULL.

缺点:将预设和自定义类型混合在一起可能不是一种很好的规范化做法，因为预设不需要"account_id"等字段.如果我想要超过1500个预设(极不可能)，我需要计算还有别的东西.该模型还将类型表中的哨兵/占位符值用于预设和保留的预设点.

Cons: Probably not good normalization practice to mix preset and custom types together since presets don't need some fields like 'account_id', etc. If i ever wanted more than 1500 presets (highly unlikely) I'd need to figure something else out. This model also uses sentinel/placeholder values in the types table for presets and reserved preset spots.

CREATE TABLE accounts (
    account_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    # Other Columns...,
    PRIMARY KEY (account_id)
    );  
CREATE TABLE widgets (
    widget_id   INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id  INT UNSIGNED NOT NULL, 
    type_id     INT UNSIGNED NOT NULL,
    PRIMARY KEY (widget_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE,
    FOREIGN KEY (type_id) REFERENCES types(type_id)
    );  
CREATE TABLE types (
    type_id     INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id  INT UNSIGNED NOT NULL, 
    name        VARCHAR(100) NOT NULL,
    PRIMARY KEY (type_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    );

型号2

用于预设和自定义类型的单独的窗口小部件类型表. "widgets"表的预设类型和自定义类型具有可为空的FK字段. Check约束可确保其中一个为null，另一个为非.

Model #2

Separate widget-type table for preset and custom types. 'widgets' table has nullable FK field for preset type and custom type. A Check constraint makes sure one of them is null and the other is not.

优点:数据库中只有1个额外的表.除了可能为空的FK之外，没有其他值/占位符值.无需保留预设值空间，并且对将来的预设类型附加组件没有限制.

Pros: only 1 extra table in the DB. No sentinal/placeholder values except maybe a nulled FK. Don't need to reserve preset value spaces and no limit to future preset type additons.

缺点:在widgets表中，每个记录使用一个FK null来作为预置类型ID或自定义类型ID.

Cons: Uses one FK null per record in widgets table for either preset_type_id or custom_type_id.

CREATE TABLE accounts (
    account_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    # Other Columns...,
    PRIMARY KEY (account_id)
    );  
CREATE TABLE widgets (
    widget_id       INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id      INT UNSIGNED NOT NULL, 
    preset_type_id  INT UNSIGNED DEFAULT NULL,
    custom_type_id  INT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (widget_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE,
    FOREIGN KEY (preset_type_id) REFERENCES preset_types(preset_type_id),
    FOREIGN KEY (custom_type_id) REFERENCES custom_types(custom_type_id),
    CHECK ((preset_type_id IS NOT NULL AND custom_type_id IS NULL) OR (preset_type_id IS NULL AND custom_type_id IS NOT NULL) )
    );  
CREATE TABLE preset_types (
    preset_type_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    name            VARCHAR(100) NOT NULL,
    PRIMARY KEY (preset_type_id)
    );  
CREATE TABLE custom_types (
    custom_type_id  INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id      INT UNSIGNED NOT NULL, 
    name            VARCHAR(100) NOT NULL,
    PRIMARY KEY (custom_type_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    );

型号3

使用中间表widget_preset_types和widget_custom_types.如果小部件具有预设类型，则将在widget_preset_types表中引用它；或者，如果小部件具有定制类型，则将在widget_custom_types表中引用.

Model #3

Uses intermediary tables widget_preset_types and widget_custom_types. If a widget has a preset type it will be referenced in the widget_preset_types table, alternatively if the widget has a custom type it will be referenced in the widget_custom_types table.

优点:可能是标准化程度最高的模型.切勿使用Null或FK Null.没有使用哨兵/地方骗子的值.

Pros: Probably the most normalized model. Never uses Nulls or FK Nulls. No sentinal/placehodler values used.

缺点:仅在DB中添加3个表以确定小部件类型.除了我的数据库中具有自定义/预设类型的小部件以外，我还有其他东西，这意味着我可以使用该模型向我的数据库中添加至少12个额外的表.过度规范化了吗?我必须使用某种类型的联接来获取所有窗口小部件信息，并同时从3个表中键入信息.我将不得不检查custom_type_id或preset_type_id是否返回联接中，使用的代码可能比检查Model#2中的空值要多.可能比Model 1和amp; 2.表格越多，索引越多，内存越多.

Cons: Adds 3 extra tables in the DB just to determine widget-type. I have other things besides widgets in my DB with custom/preset types which means I could be adding at least 12 extra tables to my DB with this model. Is it over-normalized? I'll have to use some type of join to get all widget info and type info from 3 tables at the same time. I'll have to check whether or not a custom_type_id or preset_type_id comes back in the join probably using more code than I would have used checking for nulls in Model#2. Probably slower than Model 1 & 2. More tables means more indexes means more ram.

CREATE TABLE accounts (
    account_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    # Other Columns...,
    PRIMARY KEY (account_id)
    );  
CREATE TABLE widgets (
    widget_id       INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id      INT UNSIGNED NOT NULL
    PRIMARY KEY (widget_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
    );
CREATE TABLE preset_types (
    preset_type_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    name            VARCHAR(100) NOT NULL,
    PRIMARY KEY (preset_type_id)
    );  
CREATE TABLE custom_types (
    custom_type_id  INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id      INT UNSIGNED NOT NULL, 
    name            VARCHAR(100) NOT NULL,
    PRIMARY KEY (custom_type_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
    );
CREATE TABLE widget_preset_types (
    widget_id       INT UNSIGNED NOT NULL UNIQUE, 
    preset_type_id  INT UNSIGNED NOT NULL, 
    PRIMARY KEY (widget_id),
    FOREIGN KEY (widget_id) REFERENCES widgets(widget_id) ON DELETE CASCADE,
    FOREIGN KEY (preset_type_id) REFERENCES preset_types(preset_type_id)
    );  
CREATE TABLE widget_custom_types (
    widget_id       INT UNSIGNED NOT NULL UNIQUE, 
    custom_type_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (widget_id),
    FOREIGN KEY (widget_id) REFERENCES widgets(widget_id) ON DELETE CASCADE,
    FOREIGN KEY (custom_type_id) REFERENCES custom_types(custom_type_id)
    );

推荐答案

一些非常好的设计人员在外键中使用NULL，而不会带来不利的后果.我自己就那样倾斜.可为空的FK表示可选关系.在实体没有关系的情况下，FK包含NULL.空间开销很小.当在两个表之间进行联接(更精确地说是等联接)时，FK中包含NULL的实例将退出联接，这是适当的.

Some very good designers use NULLs in foreign keys with no adverse consequences. I lean that way myself. A nullable FK represents an optional relationship. In instances where the entity has no relationship, the FK contains a NULL. The space overhead is minimal. When joins (equijoins, more precisely) are done across the two tables, instances containing NULL in the FK will drop out of the join, and that's appropriate.

话虽如此，我将向您推荐第四种方法.这总共涉及4个表，帐户，窗口小部件，类型和custom_types. custom_types表使用一种称为Shared-primary-key的技术，如下所述.

Having said that, I'm going to recommend a fourth method to you. This involves a total of 4 tables, accounts, widgets, types, and custom_types. The custom_types table uses a technique called Shared-primary-key, outlined below.

CREATE TABLE accounts (
    account_id  INT UNSIGNED AUTO_INCREMENT NOT NULL, 
    # Other Columns...,
    PRIMARY KEY (account_id)
    );  
CREATE TABLE widgets (
    widget_id   INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id  INT UNSIGNED NOT NULL, 
    type_id     INT UNSIGNED NOT NULL,
    PRIMARY KEY (widget_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE,
    FOREIGN KEY (type_id) REFERENCES types(type_id)
    );  
CREATE TABLE types (
    type_id     INT UNSIGNED AUTO_INCREMENT NOT NULL,
    account_id  INT UNSIGNED NOT NULL, 
    name        VARCHAR(100) NOT NULL,
    PRIMARY KEY (type_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
CREATE TABLE custom_types (
    type_id     INT NOT NULL,
    account_id  INT UNSIGNED NOT NULL, 
    PRIMARY KEY (type_id),
    FOREIGN KEY (type_id) REFERENCES types(type_id),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)

);

custom_types中的type_id列是共享的主键.注意，它被声明为主键和外键，并且不使用自动编号.它是相应条目类型的主键的副本.定制类型表包含定制类型中存在但预设类型中不存在的所有数据.

The type_id column in custom_types is a shared primary key. Notice that it is declared BOTH as a primary key and as a foreign key, and that it doesn't use autonumber. It's a copy of the primary key in types for the corresponding entry. The custom types table contains all the data that is present in custom types but absent in preset types.

对于预设类型，在类型中创建一个条目，但在custom_types中不创建任何条目.对于custom_types，首先在类型中创建一个条目，然后将type_id的结果值与account_id一起复制到custom_types中.

For preset types, an entry is made in types, but no entry is made in custom_types. For custom_types, an entry is first made in types, and then the resulting value of type_id is copied over into custom_types, along with the account_id.

如果您使用INNER JOIN类型和custom_types，则预设类型将从联接中退出.如果要在单个联接中同时使用自定义和预设类型，则必须使用LEFT JOIN或RIGHT JOIN来获得这种效果.请注意，LEFT或RIGHT JOIN的结果将包含一些NULL，即使这些NULL没有存储在数据库中也是如此.

If you INNER JOIN types and custom_types, the preset types drop out of the join. If you want both custom and preset types in a single join, you have to use a LEFT JOIN or a RIGHT JOIN to get that effect. Note that the result of a LEFT or RIGHT JOIN will contain some NULLs, even though those NULLs are not stored in the database.

点击此共享-primary-key 将为您提供有关共享主键技术的更详细说明.

Clicking on this shared-primary-key will give you a more detailed description of the shared primary key technique.

这篇关于有关可空外键和规范化的数据库设计问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！