Good morning, this question is somewhat difficult for me to explain, but I will do my best. I have a project in which I must obtain data from different branches of my company, for each branch, it is a database (they have the same structure, so there is no problem). My problem is that the databases of each branch were not prepared to be worked together , that is, although the structure is the same, the data was not prepared for it, or in a few words:
customer 10 of branch 1 is not the same as customer 10 of branch 2
So, when I want to apply primary keys to my tables, I have the problem that I have repeated IDs, although they do not exactly mean that they are duplicate data, since they come from different branches.
The first thing I thought of was creating one more field to manage it as an internal ID, but in my office they told me that this option is not viable at all.
So, I was thinking of creating a table called branches, which obtains data from the branch, and having the option to identify where the data comes from, adding in each table a field that serves as a foreign key for that branch table. What doesn't seem like a good idea to me is that the happy branches table has 30 relationships for all the other tables in the system. With all this already explained, the question is:
What is the best option to work with this data? I need to identify them as unique, and know which branch they come from
The truth is that you have very good ideas, I will tell you about my case in my workplace, maybe it will help you make a better decision.
In our company we have subsidiaries, Subsidiary El Salvador, Subsidiary Mexico, Subsidiary Guatemala etc etc. In each one of them we have different types of Clients: Local Clients, External Clients, Suppliers, etc. The fact is that the same Customer can be a Provider at the same time.
The solution to this case taking into account that you have Branch_1 Client: Cod. 10 and Branch_2 Client: Cod. 10 that are differentiated by The branch take into account as the first different field, taking this into account you can create a group of Clients that refers to to the Branch from which they come, even if they have the same type of code, they will carry a different type of grouping.
Greetings.