Database structure

Question

SQL

Please tell me about the structure of the database. It is necessary to store information on films.
Each film has a lot of information to relate to.

namely:

Title
Original title (eng)
Year
Country
Slogan
Director in the set
Script (in the set)
Producer (in the set)
Cinematographer (in the set)
Composer (in the set)
Artist (in the set)
Editing (in the set)
Genre (in the set)
Budget
Fees
Viewers
Example
MPAA rating
Duration

there will be many such films (about 80,000), and almost all of the same type, so you need to somehow correctly compose the DB so that there are no problems in the future.

how I thought to do it:

The main table (catalog) in which we enter

id - the number of the movie
name - the name
name_original - the original name
type - the type of picture (movie, TV series)
year - the year of release

confuses me here whether it is necessary to keep here (name, name_original) ... or move it to a separate table

table with parameters (catalog_properties)
film_id — film number
property_id — parameter name
property_value — parameter number

table with parameter names(catalog_properties_name)
id - parameter number
name - parameter name
code - parameter code for the internal needs of the frontend

+ several tables of directories, for countries, genres, etc.

table for parameters that are not included in directories (catalog_properties_data)
id - parameter number
name - parameter name

here here it confuses me that all parameters will be kept in a field with the varchar type ,
but it would be nice for
the budget, fees in the USA - interger,
premiere - date,
MPAA rating - enum

except to make a table for the parameters like this:

id - parameter number
name_string - parameter name
name_integer - parameter name
name_datetime - name of the parameter
name_enum - name of the parameter

and choose in the future like this " SELECT CONCAT_WS('', name_string, name_integer, name_datetime, name_enum) as name " but is it good ??

+ I still don’t know what to do with the description of the films, it will be the TEXT type , whether to put all the descriptions in a separate table, or push them into the parameter table, which is indicated above

Answer 1

> here it confuses me whether it is necessary to keep (name, name_original) here ... or put it in a separate table , of
course, it is better to store it in the same table
> and choose in the future like this “SELECT CONCAT_WS('', name_string, name_integer, name_datetime, name_enum) as name" but is it good??
I personally would not complicate things, let it be better varchar, 80k records are not so many, but if you still want to split, I think it would be more correct to create 3 tables for each desired type, i.e. catalog_properties_data_string, catalog_properties_data_int, catalog_properties_data_datetime and add the type field to the catalog_properties table accordingly

Answer 2

Yes, 80 thousand is quite a bit ...
If the database is designed with search in mind, then I recommend later using the sphinx, which creates its own index and when querying, it almost doesn’t matter what the database structure is.

Answer 3

You don't have to share it like that. 80k is a penny. But:
1. be sure to set up the indexes.
2. if in 90% of cases you only need the title + director, then you do not need to write SELECT *… List all the required fields.
As a last resort, you can split one table into two with a one-to-one relationship. The first should contain those fields that are always used (id, title, director), the second should contain all the rest. This will allow you to write SELECT * FROM `t1`.