1

Today I was practicing database design, for example I was trying to build a database design where I would like to store my favorite shows. I have normlaised this database to the third form (at least I think I have). But I have faced a problem with this design, actually, how can I preform a such query:

  • Is there any show that has language (let's say Italian) and status Emitting and there are more than 5 episodes available?

I think I have made a mistake with the language and show_internation_data table, but I am not sure...

Also if you could tell me how bad this design is and what to improve, or just throw an awesome article, that would be awesome!!

Diagram:

Here is the image: https://i.stack.imgur.com/abhwx.png

Improve this question
4
  • That structure has some issues too...Do you really need to normalize title? Are multiple different shows going to share a title?
    – JNK
    Dec 5, 2013 at 21:00
  • I have made it like this because a show can have multiple titles (alternative titles), it could allow for more global searches (Like CSI: ... or Crime Scene Investigation: ...). Also NF1 is telling that you have to separate the data that repeats, I got often 1 show with two titles on my UNF diagram so I though this is how it should be. Is this how you should design a (1-m) relationship?
    – Tony
    Dec 5, 2013 at 21:04
  • 1
    Yeah if it's 1:m that makes sense.
    – JNK
    Dec 5, 2013 at 21:14
  • Ohh that's a good news. Could you spot any other problems perhaps? Do you think this design would allow for the query I have described above?
    – Tony
    Dec 5, 2013 at 21:21

1 Answer 1

Reset to default
1

By my reckoning, then you should not have show_languages between show and show_international_data because it's not an association table between the two, and because you do not want the language column to be filled out for each association between a show and show_international_data. Basically if you have 10.000 shows and 20 languages, you'll need 20 x 10.000 versions of the 'language' field filled - lots of redundant data and chance for mistakes. Also associations tables like that are usually used for n:m relationships and not 1:m and because one shows international data cannot belong to another, it shouldn't be mapped as n:m.

Instead show_international_data should be connected directly to show with show_id being the FK in it. Then you can make a language table (language_id, language) and have that language_id being the second FK in show_international_data, which combined makes up it's PK. So something like this:

show -> show_international_data <- language.

In this manner you can reuse your language table on everything which needs to be localized with a translation/details (international_data) type structure without.

I don't know what recommendations is going to contain, but that might also need to be localized?

show_title will most likely also need to be localized, because titles differ between countries/languages as some countries dub or completely reworks titles of shows. So that title properly needs to be put into international data and thereby removing that table. Also you can put an original_title on the show base table.

If you do need to have alternative titles on top of this, then a secondary title table will start to make sense, and then you should - most likely - localize it by having a language_id FK enabling multiple alternative titles per language.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.