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.
Your second choice of adding a Branch ID is very similar to the Employee – Supervisor relationship. You’re just joining a table to itself. There are ways to recursively query these data if you had multiple levels of branches (Is this company a branch to a company that is also a branch to yet another company?). The data schema also hold up for this because a single company cannot fall under more than one main office.
I don’t think performance is an issue as long as you index your data according to how you want to retrieve it. Having a lot of indexes on a company table should be that big of a problem because I doubt you’ll be changing companies or adding branches that often. If you company is so certain they’ll never have multiple groups, I don’t see them having thousands of offices either.
If you go with the first option (a branch table), your would not have to change the employee. The employee is assigned to one company and just because that company has a role as a main company or a branch doesn’t change the user. You would just interpret those data based on the company having a parent company.
Have you considered adding a Hierarchy table? In this way, you can have as much levels as you want. All the reports can be done with recursion.
Also, if you want to separate levels of companies (let’s call them business units), you can have a code book of business unit types (office, branch, region,…), and all business units have their type.
Now, I’m not completely sure how user types apply here. If you don’t want to separate employee types (operative, management, …), then all that is left to do is separation of user types. Based on user type (or employee type), you can handle GUI for reports, or build these rules into a stored procedure.