I am trying to think of a good solution for modeling customer data.
The scenario is a bit complex: we have a table with the customers and their information (ID, City, ZIP, State, Address, etc). But many times these customers have branches. So the sales kpis can be reported by customer, or by the customer's branch.
In this way, there is another table that contains the branches, where for each one there is another ID, and then address, zip, city, state, etc. (there is also an ID that links both tables).
What do you think is the best way to model this? So far we have been working with separate tables, giving some problems when creating reports, and also to relate these tables. Sometimes I need data from both tables and it is not so easy to satisfy the requirements (some of them very abstract, I must admit)
My intention is to create a master table that has both IDs. Does anyone have experience with this?
