SQL Database Design Union Data Type?

Question

This may be a basic question, but I'm curious about what the most reasonable "preferred" way to design a database with a union type is.

What I mean is consider a table Post which has only 1 piece of attached content and the attached content can be of type video, audio, pdf, text or whatever. The normalized way would be create a table for each of these content types. But there needs to be a connecting table to query all of the other tables type field indicating the type of content.

That means each of these need to be queried when the initial table is queried.

Another way of handling this is having a kitchen sink content table with a type field on it.

I ask because I'm not particularly versed in database design. I'm curious what a normal pattern would be for data structured this way.

Answer 1

This type of "one-of" modeling is tricky. Some databases have built-in support for such functionality, such as inheritance.

One method is to have an attachments table and to combine all into one table. This would have columns such as:

• attachments_id
• type check type in ('video', 'audio', . . .)
• columns needed for any of them.

More commonly, each of these would be a separate entity, because the columns describing them would be quite different -- and often have their own relationships. In this case, a simple method for a handful of types is a separate column for each one, along with a constraint that at most one is not NULL:

check ( (case when video_id is not null then 1 else 0 end) +
        (case when audio_id is not null then 1 else 0 end) +
        . . .  <= 1
      )

This allows properly declared foreign key relationships.

Another method is a variation on this. Assuming that all the id columns have the same type, then use the columns:

attachment_type varchar(255) check (attachment_type in ('video', 'audio', . . .),
attachment_id int

The type would determine the type of id, but the database would not validate this.

And another method is a more complex inheritance pattern, which involves setting up individual tables for each entity, along with a type in each one. And then setting up an attachments table. This looks like:

attachments_id int generated always as identity primary key,
attachment_type varchar(255) check (attachment_type in ('video', 'audio', . . .),
unique (type, attachments_id)

And then:

video_id int primary key,
type varchar(255) generated always as
foreign key video_id references (type, attachments_id)

and so on for the other tables. This sets up a foreign key relationship to attachments and makes sure that the types match.