The structure of the database


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
3 views
Please tell me the structure of the database. Need to store information for movies.
For each movie, to treat a lot of information.

namely:

Name
Original name (eng)
Year
Country
Slogan
The Director in the set
The script (in the set)
Producer (multiple)
The operator (in the set)
The composer (set)
The artist (in the set)
Installation (set)
Genre (in the set)
Budget
Fees
The audience
Example
the MPAA rating
Duration

these films will be a lot (about 80 000), and almost all of the same type, so need to write a DB, that would not be problems in the future.

as I thought to do:

The main table (catalog) in which a recorded

id number of the film
name — the name
name_original — original title
type — the pattern type (movie, series)
year — the year of issue

here I am confused here do I need to keep (name, name_original)... or put it in a separate table

table with parameters (catalog_properties)
film_id — room movie
property_id — number parameter name
property_value — the number of the parameter

a table with the parameter names (catalog_properties_name)
id parameter
name — name of the parameter
code — the option code for the internal needs of the frontend

+ several directory tables for countries, genres, etc

table for parameters that are not included in the reference (catalog_properties_data)
id parameter
name — name of the parameter

here me confuses that all settings will be kept in a field with varchar type
and it would be good for
budget, fees in the US — interger,
premiere — date
rating MPAA — enum


except to make a table for the parameters like this:

id parameter
name_string — name of the parameter
name_integer — name of the parameter
name_datetime — name of the parameter
name_enum — name of the parameter

and choose in the future, so "SELECT anyone of parameters(", name_string, name_integer, name_datetime, name_enum) as name" but it's good right ??

still don't know what to do with the description of the film, this is the type of TEXT, whether to make a separate table all descriptions, or to cram in a table as above
by | 3 views

3 Answers

0 like 0 dislike
> here I am confused here do I need to keep (name, name_original)... or put it in a separate table
\r
of course, it is better to store in the same table
\r
> and choose in the future, so "SELECT anyone of parameters(", name_string, name_integer, name_datetime, name_enum) as name" but it's good right ??
\r
personally I wouldn't complicate things, it's better to be varchar 80k records is not as well much, but if you still want to split then I think it is correct to create 3 tables for each of the required type i.e. catalog_properties_data_string, catalog_properties_data_int, catalog_properties_data_datetime and, accordingly, to add the type field in the table catalog_properties
by
0 like 0 dislike
Yes, 80 K is quite a bit...
If the database is designed keeping in mind the search, I recommend in consequence to use Sphinx, which creates it's own index and when you query almost all the same what is the structure of the database.
by
0 like 0 dislike
Do not be so divided. 80k is peanuts. But:
1. be sure to adjust the indices.
2. if in 90% of cases need only the name + producer, it is not necessary to write SELECT *... List all the required fields.
\r
In the extreme case, can divide one table into two with a relationship one-to-one. The first should be those fields that are always used (id, title, Director), and secondly everyone else. This will allow you to write SELECT * FROM `t1`.
by

Related questions

0 like 0 dislike
6 answers
0 like 0 dislike
3 answers
asked Mar 25, 2019 by Amka
0 like 0 dislike
1 answer
asked May 22, 2019 by ars-bars
110,608 questions
257,186 answers
0 comments
27,842 users