I am putting together the EER diagram of an ERP. Virtually all tables have a column named "id_company" that relates to the "companies" table. This is because the user can manage one or more company, and I want every record of the other tables to be specific to that company.
Is it okay to insert the "id_company" column in almost every table or should I do this in some other way?
I'm going to ask you a question that will help:
Is it right to insert the "id_company" column in almost every table?
Only you can say this. Only you know the requirements of your system. If there is any reason to have these columns in almost all tables, that's right.
Probably what you're doing is called multitenancy . The opposite of this is having a database for each company and not mixing the data. Each of these approaches has both advantages and disadvantages.
It is not very common to do this, I would avoid but I do not know your case. Without getting too much into the details that is not the focus of the question, the main advantages are:
The main disadvantages are:
But there is a solution to everything. It's just a matter of what's easiest for your situation.
Can the user manage more than one company at a time? How is this handled in the application? This helps decide.
With the additional comments I would say that's right. I still say that I can not speak without knowing the whole situation. But if "Company" means branch it makes more sense to keep everything in the same database and obviously each entry line will need to have an ID of which branch it belongs to. It has to know how to filter the data whenever it is to work with just one affiliate - this is usually more common - but it seems to be a small work close to the advantages of having everything together for manipulation by the same person.
If you need to issue business-to-business reports or unified reports (eg Gross Profit Summation) you will need at least the tables related to product, service and financial movement. The registers are at the discretion of the requirements (if all the companies will have the same products, for example).
The other possibility that I can imagine for an operator to have access to several companies would be to dynamically create one bank for each company and another to register the operators and their permissions for each area of each company.
What will determine which method to use is business integration.
I own a client where each bank in each branch is in a different city, I presented the possibilities and he opted for a third one ... The bank of each branch is totally independent of the other ie the operator that exists in a does not exist in the other.
Personally I did not like it, but he is the one who chooses what he wants, and if he has to change later, I charge him again, hehehe.
