I suggest something modular, avoiding atrelating the structure to localized business rules.As an example, I would not create 2 tables, one for CPF and another for CNPJ, even if it was a localized system. I suggest something generic:person
id (unique)
date_created
date_updated
other_column1
other_column2
person_document
id (unique)
date_created
date_updated
person_id (relacionado com person.id)
document_id (relacionado com document.id)
data char(50)
document
id (unique)
date_created
date_updated
country_id (relacionado com country.id)
title (nome do documento)
country
id (unique)
date_created
date_updated
title (nome do país)
Many programmers think that when they have many tables the system is more complex, complicated, heavy, etc. But this is not good arguments because it does not influence performance in a relevant way. To understand, it follows the bed below.When you're making a small system in which you're sure you'll never use it for something bigger and mostly international, you don't have much sense to set up this entire structure. But anyway it is a structure that works well in a small and local project as a large or internationalized project. So why not normalize within a flexible pattern that serves for both cases instead of creating something "wrapped" that will not be reusable?This is one of the reasons for normalization.Explaining the above structure better.We have the table of people (person). In this table there is no information on which documents the person has. In the table documents, is where the documents are registered, however, without still linking with the table person. In this table, all documents required by the business model will be registered.The important thing here is not to dare the structure to the business model, that is to prevent it from being located.In this document table, the various types of documents will be registered. CPF, CNPJ, RG, Driver's Wallet, Passport, etc.Table person_document is where relationships are made. In this table, only the number or serial code of the document will be registered. What identifies the document, is the column document.id.The column data It's like char(50) because not all documents are numerical. There are many types of documents that include alphanumeric characters. That's why the CHAR guy is more suitable.Important also to note that the documents table is related to the country table. This makes it possible to use the system for a business model where it is necessary to know the country of origin of the document independent of the nationality of the person.That's just a short form. Of course it is possible to improve, make some adjustments, etc. But it does not mean that you must do that and you do not have that claim to be the ultimate solution.With this generic structure, the system becomes more flexible and portable. You can enjoy the same system for use in any country without having to modify the structure. Otherwise, if you do something torelate the local rules, for example, with columns or tables specific to CPF, these tables or columns will be useless to other countries and often useless even within Brazil for cases of underage registration, immigrants, tourists, refugees, undocumented, temporary residents, etc.It may seem a little strange to the table country related document, because it could relate country with person, right? The problem with this is to induce the system by disregarding people with multiple nationalities or even if they do not have the same dual nationality, they may have documents from other countries.Note also that in all tables of the example I added the columns date_created and date_updated. Many systems follow this as standard. Always have a column that reports the date and time it was registered and updated.
This is a subject that deviates a bit from the subject, as it is related to activity log and optimization. If you prefer, you can ignore it.In the original question table tb_pessoa, there is a column pessoa_tipo. This is also a way to "engage" the structure. For something more flexible, the ideal would be another table to define the type of the person. I prefer not to comment because it would become something repetitive by following the same logic presented in the proposed structure. And so it follows for any other columns or tables that can make the system (drawn) located.You really need so much? Even in a small project?Here we talk again about whether this is really necessary. If you just want to build a box control system of a small corner bar on a periphery of a small town in the interior that has no more than 5,000 inhabitants, why complicate it all?
For I repeat again what was commented some paragraphs above that, this structure serves both for a small project and for a large project. There may be a difference in performance but it is irrelevant. After all, in a small project there will be no big flow. Therefore, the concern for performance is irrelevant. In a larger project will require much more than that to get a good performance.The key point here is portability. You can use the same structure for diverse business models.obs: The above examples are merely didactic.About nomenclature, see this link: https://pt.stackoverflow.com/questions/138452/nomenclatura-de-tabela-e-coluna/138528#138528