Hello. There is approximately the following table with films films :

 +-----+---------------+----------+ | id | premiere_date | duration | +-----+---------------+----------+ | 432 | 2015-10-11 | 68 | | 433 | 2015-10-28 | 125 | +-----+---------------+----------+

The id field in it is incremental and is the primary key. There is also a table with the names of films films_titles :

 +---------+----------------+------------+------------------------------------------+ | film_id | title_language | title_type | title | +---------+----------------+------------+------------------------------------------+ | 432 | en | 1 | Intouchables | | 432 | ru | 2 | 1+1 | | 432 | ru | 3 | Неприкасаемые | | 433 | en | 1 | The Hunger Games | | 433 | ru | 2 | Голодные игры | | 433 | de | 2 | Die Tribute von Panem – The Hunger Games | +---------+----------------+------------+------------------------------------------+

The title_type field is the type of movie title. For example, 1 is the original language (there can be only one for the film), 2 is the official names in other languages ​​(there can be only one for each language), 3 are unofficial names.

Actually, the main question: how would you improve these tables?

Now all the tables are in my InnoDB, through the foreign key I connected the film_id field with the id field. Maybe on some fields you can hang indexes? I read about them, but I just can’t understand real use cases. You can also do something so that you can't add two titles with the same film_id , title_language and title_type ? To, for example, not accidentally add two official English film titles, which, logically, should be only one.

UPD. Table films will have a little more than 30.000 entries, and film_titles - about 3 times more.

Thank you in advance.

  • Can make the original title in the table of the films themselves. Surely in some list only it glows, it will be easier to get. And already additional names like now. And such a uniqueness restriction can be checked only by triggers - Mike

1 answer 1

You can and the story, but why? :) Indices need to be built on the basis of which queries are coming up and which ones need to be accelerated (and which ones are not yet). Don't fix what ain't brocken :)

As for the restrictions on film_id , title_language , title_type , then you need to add a UNIQUE index to these three fields.

 CREATE UNIQUE INDEX films_titles_uniq ON films_titles (film_id, title_language, title_type);
  • I understand that in this case I can’t add more than one unofficial film title in one language. Just when the film is not officially released in the country, it is translated by fandabbers or fansabbers, who themselves decide how to translate its name. Real examples do not get into the head, but I think you understand. In this case, it would be good to add all popular translation options to the database. - neluzhin
  • Yes. If you need this, then in PostgreSQL it can be done using partial indexes (partial index), and in MySQL - alas. As an option - split the table with names into two - with multiple names (informal) and with unique (original). In fact, even official transferable can be several (it happened in the 90s). - monoid