Modeling of launches using customers and suppliers

Navigation
2

Contextualization:

Initially I'm developing a posting module (accounts payable and receivable). Every release must relate to a vendor or customer.

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

Possible ways I found to model the case

  • Create in the "Launch" table a column named "Interface", which will receive values from an ENUM ("Customer", "Vendor"). Something like 0 (client) or 1 (provider).
  • Create two columns ("Client" and "Vendor") in the "Launch" table.

    • The problem

    I have identified the following issues according to the forms I found for modeling:

    • Case 1: In the same column of the "Launch" table there will be data from two different tables ("Vendor" and "Customer").
    • Case 2: There will be two columns ("Vendor" and "Customer") in the "Launch" table and that, necessarily, the two columns can never be filled for the same row - In the application, or a vendor, not both.

    In case 1, I thought about using UNION, being a select in the "Client" table and another select in the "Vendor" table.

    In case 2, I will have two columns with lots of empty data. Another thing is that every time I need to change the interface type (client or vendor) of a posting, I'll have to do an UPDATE to remove the data from one column and populate the other. For example, someone made an incorrect posting in which they informed customer rather than vendor.

    Is there any simpler way to do this?

    Follow the example of the system screen.

    ModelingIhavedonesofar

        
    asked by anonymous 24.09.2016 / 18:45

    2 answers

    2

    I have to say either having an empty column is not the end of the world . But I would not go that way. It 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 this. Although depending on how you will do this will always be necessary. Conceptually, it's not the right thing to do.

    Understand that normalization should work in your favor, never against it.

    Best option among those presented

    So if you're going to do this and stay between these two options, case 1 seems more appropriate. And I do not see any big problems in what you're doing. I can not imagine how simple it might be if you choose to follow the current line.

    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.

    Can this be a problem? You can depending on what you might need. There would be no way to respond without understanding the whole case in detail. But the biggest problem is not knowing if one day it can become a problem.

    This essentially answers the question.

    Wrong concept

    I find it possible to be a problem because this form is conceptually wrong. I know that almost everyone does this and then they get "creative solutions" (the term is even pejorative) to solve the problems.

    Nothing you do can guarantee that you will have no future difficulties. But when the concept is wrong it is easier to get into trouble in the future.

    The right concept does not determine that you have relationships with customers and suppliers. Relationships are with entities that happen to behave as a customer or vendor before an operation on your system.

    If you do this may be simpler, or at least simpler, to handle the correct way things are in the real world. And then the question dilemma disappears. Let's agree that it usually does not matter what role the entity is taking in this release. It matters that she has something to receive or pay for.

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

    Of course, you'll probably have to reformulate your entire system, and you may not even know how to do it correctly. If you think you should not make this change, okay, there goes the way you are intending and hope not to appear new difficulties.

    Finishing

    One thing I will comment on is that I do not like the term "interface". It's just a taste, but I find something that users do not understand intuitively.

    If you want to understand more about this issue of the entities and roles they play, I've already given various answers about it . And there you may see that the problem is even more complicated than you are imagining.

    I do not like to put this in response, but there are people who find this modeling controversial because it is not the most common and many people do not want to innovate. Each one does what he thinks best, I do so because I was the developer of a leading 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 beginning.

        
    24.09.2016 / 19:34
    2

    My suggestion is that instead of creating a Customer table and another Supplier table to associate directly with the Release, you create a table that represents an Abstraction associated with the Release. So there would only be a Launch key that would point to the "IndividualChange" table. For Customer or Provider-specific information, you would create a column in each of these tables pointing to "IndividualLancement" (individual_individual_lancement). To maintain a nxn relationship (multiple clients for multiple postings) you would create an intermediate mapping table between client, vendor and individual launch. This way, you keep your bank's data normalized and can identify if the individual launch is a Customer or Supplier. It would look something like:

        
    24.09.2016 / 19:20
    Select the highest value from a set of columns of a df and get the column name of this higher value in R How to read a struct inside another with Json.Net?