I give you a graphic example of what I am implementing, and I would like to know if this is a good practice.
I explain. a customer can make many payments, but in turn a partner can also make payments, then, would he have to make a payment table for each entity (clients, partners)?
Beyond whether it is good design or not, it would be better to ask:
Does this design meet the goal I'm pursuing?
I will give you two points of view, the most common ones
PROS : The main advantage of normalizing the model are the rules that you explicitly add to the business, preventing inconsistencies (possibly due to software failures) from your software. In short, a robust model.
CONS : It will be more rigid, you will have to take into account the restrictions of the model when you have to introduce changes and it will also be slower, due to the increase of unions that you will have to make between Entities (Payments, Clients, Partners, etc.) and relationships (Payment_Client, Payment_Social, etc.)
Defining a relationship based on whether the value is NULL or not, is not very good practice when you are looking for a formal model. Instead, define relationships between entities like this:
Another advantage would be that in hypothetical case of adding another type of "profile" of payment, with creating another relationship you would already have it ... without adding another field to null;)
PROS : Access to information is direct. The information is easily recovered and you should consider what calls you are making and depending on what information you recover at any time, determine if it is worth "de-normalizing" the model in favor of speed (if that is what you want).
CONS : When the model is de-normalized, the information begins to duplicate (especially the keys) and the responsibility for the referential integrity of the information falls on the data / business software layer. It is possible that bugs compromise the information and / or
In this case and it is usually the case of many web pages with a high number of visits, it is sometimes your turn to denormalize the model based on the response time you want to obtain. I do not know if it will be your case.
Your example is closer to that situation, quick access to information ...
Here you have to ask the following questions:
In the first case, if you check the payments all "together", your model will work. In the second case, if you really have many records and your query takes a long time, you should de-normalize and perhaps apply techniques SHARDING (the classic divide and conquer)
For example, applying horizontal Sharding on your table, the most obvious thing is to divide payments from partners of customer payments ...
I hope it will help you decide in relation to the objective you are pursuing.