我的数据结构如何？ [英] How's my data structure?

问题描述

我建立了一个简单的数据库来存储有关奖项和提名的信息。我试图尽可能多地删除数据冗余。以下是目前的内容：

I've put together a simple database for storing information on awards and nominations. I've tried to remove as much data redundancy as possible. Here's how it's presently looking:

提名表的原因是我意识到一个提名会有很多提名人。例如，最佳剧本可以转到 Ken Levine和David Isaacs 或 Woody Allen 或 Joss Whedon，Andrew Stanton，Joel Cohen和Alec Sokolow 。

The reason for the Nominated table is that I realised that one nomination would have many nominees. For example, the award Best Screenplay could go to Ken Levine and David Isaacs or Woody Allen or Joss Whedon, Andrew Stanton, Joel Cohen and Alec Sokolow.

Award.name 是奖项的名称，例如

Note: Award.name is the name of the award, e.g. Best Actor.

感谢您指出任何可能的改进。

Thanks for pointing out any possible improvements.

推荐答案

小写笔记

1. 被提名人成为被提名者，奖 > Award 表

1. I prefer singular for table and column names so Nominees becomes Nominee, Awards becomes Award, etc.

将

---

主要注释

---

Major notees

如@Olivier指出， m :: n 关系中间如提名，将对复合（NomineeId，）有 UNIQUE NominationId）。因此，最好删除自动生成的（代理）键，并使复合键 PRIMARY KEY 。这是关系的自然键，使用它作为主键有几个好处。在这种情况下，代理键不起作用，除了具有更宽的行和更多的无用的指数。

As @Olivier points out, the m::n relationships intermediate tables, like the Nominated one, will have a UNIQUE constraint on the compound (NomineeId, NominationId) . So, it's better to drop the auto generated (surrogate) key and make the compound key the PRIMARY KEY. This is the natural key of the relation and there are several advantages of using it as the Primary Key. The surrogate key serves no purpose at all in this case except for having wider row and one more useless index. The two parts of the natural keys will be used for joining anyway.

同样的情况也适用于提名表！ （FilmId，AwardCategoryId，EventId）将是 UNIQUE 键，以确保没有电影获得2个提名同一事件的同一奖项类别，所以它再次更好地删除代理键，使这个复合的主键。对于同一电影，我们可能为同一AwardCategory提供2个提名，比如两个'最佳配对演员，因此我们添加 NominatioNo 在主键（如果我们想限制某个类别的提名，或者所有人说常数5，这可以很方便）。

The same thing applies for the Nomination table! The compound (FilmId, AwardCategoryId, EventId) will be a UNIQUE key, to ensure that no film gets 2 nominations for the same award category for the same event, so it's again better to drop the surrogate key and make this compound the primary key. Rethinking, we may have 2 nominations for the same AwardCategory for the same Film, say for two 'Best Supporting Actor' so we add a NominatioNo in the Primary Key (this can be handy later if we want to restrict the nominations for a certain category or for all to say the constant 5).

现在，（有趣的和有趣的）事情是提名表必须重新审查，并有一个复合（NomineedId，FilmId，AwardCategoryId ，EventId）主键 - 只有这四列作为属性。

Now, the (funny and interesting) thing is that the Nominated table has to be re-examined and have a compound (NomineedId, FilmId, AwardCategoryId, EventId) Primary Key - and just these 4 columns as attributes.

我不确定 Event 和 Ceremony 表用于存储，但我们假设 Ceremony 用于存储关于不同仪式的信息（例如'Oscar Awards'，'Strawberry Awards'）和事件表是存储关于一年的仪式的信息（例如（'Oscar'，2011），（'Oscar'，2012） ，2012））。所以我将 Year 移动到 Event 表，并使（CeremonyId，EventYear ）活动的Priamry Key。 （我可能是错误的，你知道你的数据更好。）

I'm not sure of what exactly the Event and Ceremony table are meant to store, but lets assume that the Ceremony table is meant to store information about different ceremonies (e.g. 'Oscar Awards', 'Strawberry Awards') and the Event table is to store information about a year's ceremony (e.g. ('Oscar', 2011), ('Oscar', 2012), ('Starwberry Awards', 2012)). So i'll move the Year to the Event table and make the (CeremonyId, EventYear) the Priamry Key of Event. (I could very well be wrong this, you know your data better.).

所以， Nomination.EventId 替换为 CeremonyId 和 EventYear 以及提名和提名获得更长时间！ （这是使用自然键作为主键的一个缺点）。让我们看看到目前为止：

So, the Nomination.EventId is replaced by CeremonyId and EventYear and the Primary Keys of both Nomination and Nominated get even longer! (that's one drawback of using natural keys as Primary Keys). Lets see what we've got so far:

数据库设计1 http://img594.imageshack.us/img594/9592/oscarw.png

您可以轻松地添加 NominationWinner （与 1：1 的关系提名 ）存储哪个提名赢得了哪个类别（（CeremonyId，EventYear，AwardCategoryId）的唯一约束将强制执行）。设计如下：

You can easily add a NominationWinner (as a table with 1:1 relationship to Nomination) to store which nomination won which category (a Unique constraint on (CeremonyId, EventYear, AwardCategoryId) would enforce that). The design would be like this:

数据库设计1 http://img845.imageshack.us/img845/2108/oscar3x.png

有这么复杂的主键可能看起来很笨重，但它在连接表时有所帮助。想象一下，你想找到所有的50年代和60年代的草莓奖的获奖者，只有女演员类别，并显示该奖项是什么电影。您不必连接所有中间表。相反，您只能使用 NominationWinner ，代理，礼仪，电影和 AwardCategory 表（仅使用提名 middle table）：

Having so complex primary keys may look clumsy but it helps when joining tables. Imagine you want to find all Winners for the 'Strawberry Awards' for the 50s and 60s and only for the 'Actresses' categories and also show for what film the award was for. You don't have to join all intermediate tables. Instead, you can retrive data using only the NominationWinner, Nominee, Ceremony, Film and AwardCategory tables (and using only the Nominated intermediate table):

SELECT ne.Name                AS Winner
     , wi.EventYear           AS Year
     , aw.AwardCategoryTitle  AS Category
     , fm.Title               AS FilmTitle
FROM
      NominationWinner AS wi
  JOIN 
      Ceremony AS ce
          ON ce.CeremonyId = wi.CeremonyId 
  JOIN
      AwardCategory AS aw
          ON aw.AwardCategoryId = wi.AwardCategoryId 
  JOIN
      Film AS fm
          ON fm.FilmId = wi.FilmId 
  JOIN
      Nominated nd
          ON  nd.CeremonyId    = wi.CeremonyId 
          AND nd.EventYear     = wi.EventYear 
          AND nd.AwardCategory = wi.AwardCategory 
          AND nd.NominationNo  = wi.NominationNo 
          AND nd.FilmId        = wi.FilmId  
  JOIN
      Nominee AS ne
          ON ne.NomineeId = nd.NomineeId 
WHERE 
      ce.CeremonyTitle = 'Strawberry Awards'
  AND wi.EventYear BETWEEN 1950 AND 1969
  AND aw.AwardCategoryTitle LIKE '%Actress%'

这篇关于我的数据结构如何？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！