There are entities. Entities have information that is divided into groups.

For example: entity 1 has groups with numbers 1 and 2, in which entities are also stored.

For example: in group 1 the entity A is stored, and in group 2 the entity B. is stored. These entities are of the same type.

Groups and entities that are in groups have identical attributes that store different values.

The problem is that first-order entities, if you store attributes in a separate table and use the surrogate keys of the first-order entity tables, group tables, and entity tables in a group as foreign keys, cells with the value null appear.

It is impossible to insert another value, since there is a uniqueness restriction. Easier on a specific example -> db_series . Cells with null in tables with numbers 5 - 8.

The database is designed for the TV series site. Essences of the first order are serials, groups - seasons of serials. Entities stored in the group - this is of course a series of seasons. Identical attributes - title , keywords , description , comments, screenshots, posters.

Is there a design pattern for my case? How to get rid of cells with null ?

Can break the title and insert directly into the tables of TV shows, seasons, comments, etc.?

    2 answers 2

    I do not see a problem to keep one-to-many table entries. Just do not make the column unique, leave it with an index and that's it.

    You get the NULL error, most likely, not because of the incorrect database architecture, but because of errors in the code for writing to the database.

    ZY no special architecture is required here, everything is quite transparent in my opinion.

    • null is not an error, but a necessary measure due to a unique constraint. - Alexander Rezov

    The entities "Series", "Seasons", "Series" have a lot of common properties. You write the year to the series, but the information about the seasons and episodes of the series may be interesting. You only have the cast for the series, but in the future it may be interesting for the specific series (by the way, I recommend making the reference book of actors and a linking table in which the role can be reflected and the directors can be in the same table). On imdb.com and in many other places not only the series has a rating, but also a series.

    In total, our entities are so similar that we can conclude that they are all one and the same entity. I propose to create for the series, seasons and series one common table. We organize the table in the form of a tree. An entity can have a parent field - a season is a series, a series is a season. The show has a parent field that is NULL.

     create table db_series( id_series int not null auto_increment, id_parent int, -- ID родителя type tinyint not null, -- Тип: 1-сериал, 2-сезон, 3-серия name varchar(200), -- Название orig_name varchar(200), -- Оригинальное название prod_date date, -- Дата выхода primary key(id_series), foreign key (id_parent) references db_series(id_series) );

    All other tables for reference contain a single ID and refer to the desired level of hierarchy. Whether the Type field is needed to quickly determine what it is, decide for yourself.

    Since the hierarchy level is not large, MySQL has enough opportunities in principle to work with it calmly. Although it is better to take a DBMS that supports hierarchical queries (for example, postgresql).

    • Thanks for the decision. After publishing the question, I made the following way: serials, seasons and series were divided into separate tables and created a table of their relations, in which there are 4 fields, the first id autoincrement, and the following 3 foreign keys from the tables are serials, seasons and series. It turns out this way: the line of the series looks like this - the id of the series, null, null; season string - null, season id, null; the series string is a null, null, id series. I like your decision more, what do you think? - Alexander Rezov 4:05 pm
    • @AlexanderRezov you still have a common ID, this is correct. to pull several fields along a heap of subordinate tables - the connecting entity is obviously asking for it. "My" solution I certainly like more, minus an extra table in queries, and even with some wild left join with three tables, if the query comes from the other side (find everything where the actor was shot, for example). And "mine" in quotes, because such a structure can be traced in the same imdb.com, they have a common id in serials and episodes. seasons really somewhere apart - Mike