Modeling launches using customers and suppliers

Question

Contextualization:

Initially I am developing a launch module (accounts payable and receivable). Every release must be related to a supplier or customer.

As I wish to model both customer and supplier in detail, I will create a table called "Customer" and another "Supplier".

Possible ways I found to model the case

1. Create in the table "Lancamento" a column called "Interface", which will receive values from an ENUM {"Client", "Supplier"}. Something like 0 (customer) or 1 (supplier).
2. Create two columns ("Customer" and "Supplier") in the table "Release".

The problem

I identified the following problems according to the shapes I found to model:

• Case 1: In the same column of the table "Lancamento" will have data from two different tables ("Supplier" and "Customer").
• Case 2: There will be two columns ("Supplier" and "Customer") in the table "Lancamento" and that, necessarily, the two can never be filled for the same row - In the application, a launch is for a customer or a supplier, never both.

In case 1, I thought to use UNION, being a select in the table "Customer" and another select in the table "Supplier".

In case two, I’ll have two columns with a lot of empty data. Another thing is that every time I need to change the interface type (client or vendor) of a release, I will have to do an UPDATE to remove the data from one column and fill the other. For example, someone made an incorrect release in which informed customer instead of supplier.

Is there any simpler way to do that?

Follow example of the system screen.

Modeling I’ve done so far

Answer 1

I have to say I want to having an empty column is not the end of the world. But I wouldn’t go down that road. He has some problems and one of them is that it complicates extensibility, as Bacco said in the comments, but it may not affect you. The logic of the application will have to deal with it. Although depending on how you do it, it will always be necessary. Conceptually not the most correct.

Understand that normalization should work for you, never against.

Best option among those shown

So if you’re going to do this and get between those two options, case one seems more appropriate. And I don’t see big problems in what you’re doing. I can’t imagine how it could be simpler, if you choose to follow the line currently adopted.

I could not imagine how it could be more complicated, but I discovered here that it is possible to complicate much more than your solution.

Obviously you need to indicate somewhere what that information is. How to do this depends on some uninformed parameters.

Could that be a problem? It could be depending on what you might need. Then you wouldn’t be able to answer without understanding the whole case in detail. But the biggest problem is not knowing if one day it might become a problem.

That essentially answers the question.

Misconception

I think it might be a problem because this form is conceptually wrong. I know that almost everyone does so and then they come up with "creative solutions" (the term is really derogatory) to solve the problems.

Nothing you do can guarantee that you will not have future difficulties. But when the concept is wrong it is easier to see yourself in difficulties in the future.

The correct concept does not determine that it has relations with customers and suppliers. Relationships are with entities that by chance at a given time behave as a customer or as a supplier in relation to a transaction in their system.

Doing so may be simpler, or at least simpler to deal with the way things are in the real world. And then the dilemma of the question goes away. Let’s face it, it doesn’t usually matter what role the entity is playing in this release. It matters that she has something to collect or pay.

But if you have a reason could put the role played. You have to raise the requirements correctly. You will never make a correct system if the requirements are wrong or incomplete. In my experience they are almost always wrong.

Of course you will probably have to redesign your entire system and maybe you don’t even know how to do it properly. If you don’t think you should make this change, okay, here’s the way you’re going and hope there aren’t new difficulties.

Finalizing

One thing I’ll comment on is that I don’t like the term "interface". It’s just like, but I find something users don’t intuitively understand.

If you want to understand more about this issue of entities and roles that they exercise I have already given several answers on this. And there you may see that the problem is even more complicated than you imagine.

• It can be considered a bad modeling practice to store all people (customers, employees, suppliers) in the same table?
• What to do if client can be both PF and PJ?
• Heritage and Polymorphism

I don’t really like to put this in response, but some people find this modeling controversial because it’s not the most common and a lot of people don’t want to innovate. Each one does as he thinks best, I do so because I was a developer of a market leader ERP used in thousands of Brazilian and foreign companies of all sizes and industries. I know the headache that is conceptualizing wrong. Every day we had to invent gambiarras because the model was not conceptualized right from the start.

Answer 2

My suggestion is that instead of you creating a Customer and another Vendor table to associate them directly with Launch, you create a table that represents an abstraction of them associated with Launch. So there would only be a Launch key that would point to the "Individual" table. For Customer or Vendor-specific information, you would create a column in each of these tables pointing to "Individuolancamento" (id_individuo_lancamento). To maintain an nxn relationship (multiple customers for multiple launches) you would create an intermediate mapping table between customer, vendor and individual launches. This way, you keep your database data normalized and have how to identify if the individual launch is a Customer or Vendor. It would look something like: