第二范式问题 [英] Second normal form question

问题描述

当我进行标准化时，我对自己的思维方式没有把握.我正在为一个虚构的在线披萨店设计一个数据库.

I'm insecure about the way i'm thinking when I'm normalising. I'm designing a database for an fictional online pizza shop.

考虑一个表，该表的级联键为order_nr和pizza_article_nr.

consider a table with a concatenated key being order_nr and pizza_article_nr.

我被比萨饼的馅料卡住了.我认为从零开始，他们不依赖披萨，因为从技术上讲，它们可以独立存在.但实际上，它们总是与比萨饼相连.那么，它们是否会独立存在，以便我用3NF进行处理?或者浇头"列是否因2NF失败，因为它实际上依赖于披萨?

I'm stuck with the pizza toppings. I'm thinking that taken litterally, they don't rely on the pizza since they technically speaking can exist on their own. Yet in reality they're always connected to a pizza. So then do they exist on their own so that I will deal with them in 3NF or does the column 'toppings' fail 2NF because it does rely on the pizza in practical reality?

推荐答案

困惑的根源是，您在多个地方看到了密钥，并且您认为它必须是冗余的.事实是，在规范化中，您需要忽略密钥中的伪冗余.这不是真正的冗余，而仅仅是信息的重复.出现重复是有原因的，即表明实体之间的关系.

The source of your confusion is that you are seeing keys in more than one place and you're thinking that it must be redundancy. The fact is that in normalization you need to ignore the psuedo-redundancy in the keys. This is not real redundancy but merely repetition of information. The repetition is there for a reason, namely to indicate the relationship between entities.

如果您有一个可用的浇头表，即主键是topping_id，则该表会告诉您3NF比萨在哪个浇头上.如果您没有有一个浇头的查找表，而是将浇头的名称放在比萨饼成分表中，那么我认为很多人会说您违反了2NF.如果顶部名称不是不是不可变的，则它们是正确的.如果打顶名称恰好是不可变的，则有一个说法是打顶名称是隐式打顶表的主键.但是，从最佳实践的角度来看，最好使用无意义的密钥-除非您有个很好的理由逐案使用有意义的密钥.因此，请避免在比萨饼成分表中使用浇头名称.

If you have a table for toppings that are available, i.e. the primary key is topping_id, then a table that tells you which topping is on which pizza is 3NF. If you don't have a lookup table for toppings and instead put the topping name in your pizza composition table, then I think a lot of people would say you're violating 2NF. They would be right if topping names are not immutable. If the topping names happen to be immutable then there's an argument to say that the topping name is your primary key to an implicit topping table. However, as a matter of best practice, it's good to have meaningless keys in general - unless you can come up with a really good reason to use a meaningful key on a case by case basis. Therefore avoid using topping name in your pizza composition table.

由于您通常一次只能订购一个以上的披萨(我削减了代码并育有两个十几岁的儿子，所以我从经验上讲)，您的架构应该遵循以下原则:

Since you can often order more than one pizza at a time (I cut code and have two teenage sons, so I speak from experience) your schema should probably be along these lines:

ORDER:
  order_id (PK)
, date_taken
, deliver_to (or FK to a CUSTOMER table if you're being ambitious)

PIZZA:
  pizza_id (PK)
, order_id (FK)
, size

TOPPING:
  topping_id (PK)
, topping_name

PIZZA_COMPOSITION:
, pizza_id (PK, FK)
, topping_id (PK, FK)
, quantity (My kids insist on double cheese)
, coverage (One likes half plain cheese...)

此架构为3NF，因为唯一出现在多个位置的是外键.

This schema is 3NF because the only thing that appears in more than one place is a foreign key.

这篇关于第二范式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！