7
I’m assembling the EER diagram of an ERP. Almost all tables have a column called "id_company" that relates to the "Companies" table. This is because the user will be able to manage one or more companies, and I want each record of the other tables to be specific to that company.
Is it right to insert the "id_company" column in virtually all tables or should I do it some other way?
6
If you need to issue comparative reports between companies or unified reports (Ex: Gross Profit sum) you will need to put at least in the tables related to moving products, services and financial. Registration is at the discretion of the requirements (if all 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 a bank for each company and another only to register the operators and their permissions for each area of each company.
What will determine which method to use is integration between companies.
I own an account in which each bank of each branch stays in a different city, I presented the possibilities and he opted for a third... The bank of each subsidiary is completely independent of the other or the operator that exists in one does not exist in the other.
Personally I did not like, but he is the one who chooses what he wants, and if I have to change later, I charge again, hehehe.
5
Let me ask you a question that will help:
It is right to insert the column id_company in virtually all tables?
Only you can say that. Only you know the requirements of your system. If there is any reason to have these columns in almost every table, you are right.
Probably what you’re doing is called multitenancy. The opposite of this is to have a database for each company and not to mix the data. Each of these approaches has advantages and disadvantages.
It’s not very common to do it this way, I would avoid it but I don’t know your case. Without getting too into the details that is not the focus of the question, among the main advantages are:
The main disadvantages are:
But for everything there is solution, it is only a question of what is easier for your situation.
Can the user manage more than one company at the same time? How is this handled in the application? This helps you decide.
With the additional comments I would say that’s right. I still say I can’t talk without knowing the whole real situation. But if Company means affiliate so it makes more sense to keep everything in the same database and obviously each input line will need to have an identification of which affiliate it belongs to. You have to know how to filter data every time you work with just one branch - this is usually more common - but it seems to be a small job close to the advantages of having it all together for manipulation by the same person.
Browser other questions tagged sql database modeling foreign-key diagram
You are not signed in. Login or sign up in order to post.
This ERP does not provide the possibility to control more than one parent company. In this case, the main user (administrator) has control over the other users + affiliate registration. But as for launching products, registering employees etc. It is only possible for that session, that is, each user is connected to only one company. In this case, the administrator will always be connected only to the parent company. Did he get confused?
– Thiago Yoithi
Company and affiliate are completely distinct concepts. Did you make this modeling? Did you choose to use names in English? Do you speak English? Maybe the confusion started there. If "company" is branch, then I probably put in the same database.
– Maniero
Yes, I have fluent English, I even asked this same question in English stackoverflow, but I did not get answers. I preferred to use company "company" to portray both the parent company and the subsidiaries (because a subsidiary is still a company).
– Thiago Yoithi