I have a table Company and User like below...
CompanyID CompanyName etc...
UserID Email UserType CompanyID etc...
First I must tell you that, currently I have two usertypes, Admins (who can do anything) and Users (with some basic privileges)
Now, I have a requirement to introduce, Branch of companies. Say Company A have Branch A, Branch B, Branch C etc. and these branches can be located across geography or within same region.
Now, I have two options.. 1. Create a table Branch. 2. Add a column to Company table named ParentCompanyID
I'm more inclined towards 2, as it's a easy solution. But I'm not sure in near future whether I'll face any scaling issues while fetching reports according to this hierarchy. I just can get it done by introducing new column and a usertype SuperAdmin.
The SuperAdmins will have no role, but just to view the reports of all the Branches and branch specific reports which are already in place.
But if I go with 1, I'm not sure how to proceed, because User table has CompanyID, which I need to change to BranchID. But then again, User can be of Company (the one who can view reports of all branches).
I know, there's no best way in software engineering, but what do you think what is more approachable way for the specific case considering scaling and performance in terms of SQL Reports.