mysql – Best way to keep data that depends on a condition

Question:

Hypothetically I have a publicação table that has, by default, the attributes autores (derived from a relationship table), titulo , edição , editora and ano . However, depending on the type of publication (such as livro , artigo em periódico artigo em jornal , artigo em periódico artigo em jornal and others) there would be a need to collect additional data.

For example, if the publicação is of the livro type, it would be necessary to store quantidade de páginas and volume . However, a publicação can be generic and not have a type.

My question is: what is the best way to treat this situation?

I thought of two possible ways:

1. • Create a publicação table with default attributes.
   • Add a column tipo in a publicação
   • Create a table for each possible publication type with its particular attributes
   • Relate these new tables to the publicação and form a composite key

or

2. • Create a publicação table and add all possible attributes of the types and do the processing in the server-side application. (this doesn't seem like a good solution to me)

I have little database experience. I believe there are other solutions. Would you like to know which ones?

Answer:

There are no absolutely right solutions in software development. So the best way depends on a lot.

What can be said that the form considered more correct is the first, as it involves data normalization . More correct does not mean better. There are situations where you must do what is not so correct in order to achieve the best result for the specific situation.

In general the less "optional" information you have the better, but there can always be reasons to do this, possibly even for optimization.

Even defining what is best is complicated. Best at what? For what? For whom? When? Even if it meets one criterion, it won't be able to meet others.

Alternatives exist. For example you can go deeper into normalization, go to 6th. normal form or the form of key and value pairs . I don't recommend it, but it's a way to decouple the data.

The second form should only be taken if you have real performance issues (if measured correctly), which I doubt is the case.

My only question is about the use of the composite key. Unless there's something you didn't report I don't think you need it. The publication id can be used as the primary key in the table of each specific type.

If the publication is generic, just leave the column indicating the type unvalued, probably null.