0

I have a table Company and User like below...

Company Table:

CompanyID    CompanyName    etc...

User Table:

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • This is what I'm thinking of doing. But just a bit skeptical about performance issues in future. And yes, I'll only have one level of hierarchy i.e. Employee - Supervisor Relationship.
    – Krishnandu Sarkar
    Commented Oct 21, 2016 at 18:20
  • You can always look into how to improve the performance of a query with a self-join.
    – JeffO
    Commented Oct 21, 2016 at 20:46
0

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.

Improve this answer
6
  • Thanks for the idea. But even in near future, as much as we have thought from the business perspective, we do not need multi level hierarchy such as Companies => Regions => Branch etc. Only one level of hierarchy would do.
    – Krishnandu Sarkar
    Commented Oct 21, 2016 at 10:43
  • BTW by hierarchy table you mean a separate table with two columns such as (CompanyID, Level) right?
    – Krishnandu Sarkar
    Commented Oct 21, 2016 at 10:44
  • By hierarchy, I mean ParentCompanyID, ChildCompanyID. Every company has it's level, so LevelID should be in Company table. Maybe I'm not understanding the requirement.
    – Aleksandra
    Commented Oct 21, 2016 at 11:17
  • Let me rephase my question, I'm looking for Employee - Supervisor Relationship but for Company i.e. Company - Branch. So I'll have only one level of hierarchy. So I'm trying to understand which is the better solution to implement this. Whether to add SupervisorID in same table or introduce a new table to hold the relationships.
    – Krishnandu Sarkar
    Commented Oct 21, 2016 at 18:21
  • 1
    Separate table is a better way to go in terms of scalability. Only you can decide if you will put SupervisorID in the same table or not.
    – Aleksandra
    Commented Oct 26, 2016 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.