Is it bad practice to separate different groups of data into different tables?

Question

Database: PostgreSQL, latest version (15.1)

Imagine I have an application such that a user can upload different types of media, for example: Poems, Short Stories, and Books. Each of these are similar in the fact that they are all text Based, however they also have some slight differences. Suppose I want to implement a search feature where users can search all 3 media types. This will be done using PostgreSQL's full text search feature.

Would it be better performance wise to store all 3 media types in the same table? Or should I have 3 different tables, one for each media type.

I ask because on one hand, I hypothesize that having them all in one table will result in a potentially faster search time as I will not have to union or join multiple select statements together when getting the results. However, I may be wrong in this assumption.

On the other hand dividing the media types into different tables will allow for the structure of each table to be different based on the differences in how the application handles each media type. This seems like it would be better practice, but I am unsure on what to do.

Sorry for the long question, I just wanted to be as clear as possible.

Answer 1

As a general rule, if the data is the same between each sub-type of entity then you are better off keeping them all in the same table if you are likely to want to query over the whole set in one go (because, as you suggest, UNIONing multiple queries adds complexity.

If the types have common features but a small number of unique ones, then a single table is still OK.

If they have common features but significant extra ones that only apply to one, then look the table inheritance pattern, where you have a common table plus a table for each type with its unique properties like so:

Media                Book
-------------        -----------
Id (PK)      <------ Id (PK, FK)
Title            |   Pages
Description      |   CoverType
ISBN             | 
PrimaryLanguage  | 
SubType          |   Film
                 |   ----------------
                 '-- Id (PK, FK)
                     Runtime
                     SubtitleLanguage

This avoids having many NULLable columns that are only relevant to some or one of the sub-types.