Standard information of a record

Question

I have a table [produto] and a table [produto_embalagem], which may contain several packages of a single product.

The discussion is whether the standard product packaging should be a marking in the table [produto_embalagem] or a field [id_produto_embalagem] on the table [produto].

Answer 1

Technically it works with any of the forms. You should check which of the two forms provides the best index on the bench. This will depend on the queries that are launched in the database, so you need to analyze the applications that connect in this database and see what queries they usually launch.

For example, if the application usually needs to get the product from a certain standard packaging, the following query will repeat quite a lot:

select * from produto where id_embalagem_padrao = 123

So in this case, it makes sense that you have FK on the table produto and create an index for her.

However, if it is more common to search for the standard packaging of a given product, it will be the following query that will repeat a lot:

select * from produto_embalagem where id_produto_padrao = 123

Hence in this second scenario you will prefer to have the FK on the table produto_embalagem and have an index for her.

You should analyze your application and see which of the two situations is most common.