All geek questions in one place

How is my data structure?

I put together a simple database to store information about awards and nominations. I tried to remove as much data redundancy as possible. Here's what it looks like now:

enter image description here

The reason for the Nominated Table is that I realized that there will be many nominees in one nomination. For example, Best Screenplay rewards can go to Ken Levine and David Isaacs or Woody Allen or Joss Whedon, Andrew Stanton, Joel Cohen and Alec Sokolow .

Note: Award.name is the name of the award, for example. Best Actor .

Thanks for pointing out possible improvements.

+1
source share
2 answers

Minor notes

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

  • Renaming the Award table as AwardCategory as @wildplasser in the comments.

Key Messages

As @Olivier points out, m::n intermediate relationship tables such as Nominated will have a UNIQUE for the join (NomineeId, NominationId) . Therefore, it is better to discard the automatically generated (surrogate) key and make the composite key PRIMARY KEY . This is a natural relationship key , and there are several advantages to using it as a Primary Key. the surrogate key in this case has no purpose, except that it has a wider range and another useless index. In any case, the two parts of the natural keys will be used to connect.

The same applies to the Nomination table! The connection (FilmId, AwardCategoryId, EventId) will be the UNIQUE key to ensure that no film receives 2 nominations for the same award category for the same event, so again it is better to refuse the surrogate key and make this connection the main key . After rethinking, we can have 2 nominations for the same category award for the same film, say for two 'Best Supporting Actor' , so we add NominatioNo to the Primary Key (this can be useful later if we want to limit the nominations for a certain category or for everyone to say a constant 5).

Now (funny and interesting) that the Nominated table should be reviewed and have a composite (NomineedId, FilmId, AwardCategoryId, EventId) The main key - and only these 4 columns as attributes.

I'm not sure what the Event and Ceremony table is for storage, but it suggests that the Ceremony table is for storing information about various ceremonies (for example, the 'Oscar Awards' , 'Strawberry Awards' ), and the Event table should store information about the annual ceremonies (e.g. ('Oscar', 2011), ('Oscar', 2012), ('Starwberry Awards', 2012) ). Therefore, I will transfer the Year table to the Event table and make a (CeremonyId, EventYear) Amy event key. (I could have been mistaken, you know that your data is better.).

So, Nomination.EventId is replaced by CeremonyId and EventYear , and the primary keys of both Nomination and Nominated are getting even longer! (this is one of the disadvantages of using natural keys as primary keys). Let's see what we have:

Database Design 1 http://img594.imageshack.us/img594/9592/oscarw.png

You can easily add a NominationWinner (as a table with a 1:1 ratio to Nomination ) to save which category won which category (Unique restriction on (CeremonyId, EventYear, AwardCategoryId) will ensure its implementation). The design will look like this:

Database Design 1 http://img845.imageshack.us/img845/2108/oscar3x.png

Having such complex primary keys may seem awkward, but helps with joining tables. Imagine that you want to find all the winners at the "Strawberry Awards" during the 50-60s and only for the categories of "Actresses", and also show what movie the award was for. You do not have to join all the staging tables. Instead, you can retrieve data using only the NominationWinner , Nominee , Ceremony , Film and AwardCategory (and using only the Nominated staging 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%'
+1
source

The NOMINATED table creates an m: n relationship between NOMINEES and NOMINATIONS . Place the NominatedID column and use the two columns NomineeID and NominationID as the primary key. This does not allow the same person to be nominated twice for the same nomination.

  NOMINEES NOMINATED +---------------+ +---------------------+ NOMINATIONS | PK NomineeID |<----o| PK FK NomineeID | +------------------+ +---------------+ | PK FK NominationID |o--->| PK NominationID | | Name | +---------------------+ +------------------+ +---------------+ | FK FilmID | | FK AwardID | | FK EventID | +------------------+
+2
source

Source: https://habr.com/ru/post/1394779/

More articles:


All Articles