Using database cyclic entity design

Question

I'd like to store company data which has branch and each branch has different department. I have the following options:

1. Create Company, Branch and Department entity to store 3 different information.
2. create one entity which has the cyclic relationship and use company type to differentiate. Possible data structure: company (id, CompanyName, type, parent) possible data:

Company.

Id >CompanyName >Type >Parent

1 >Honda >HQ >NULL

2 >Honda US >B >1

3 >Honda Japan >B >1

4 >Honda US HR >D >2

5 >Honda US Sales >D >2

I am inclined to use option 2 as if there is a new requirement like Conglomerate as high level the option 2 will be able to resolve it without introducing new table.

Will this design make sense or it will cause the problem in the future? Please comment.

Answer 1

You have got two related, but separate questions to answer.

Firstly - how do I store inheritance relationships in a relational database? Company, department, and branch are all instances of "business unit" - if they are all mostly the same, you could use a single table, but if they are all rather different, a single table is often a bad design (see the other SO question for more context).

The second question is "how do I store hierarchical relationships in my inheritance model?". For consistency, I'd store this in the same model you chose for your inheritance solution; but it may make sense to have a separate "relationships" table. This would allow you to deal with unexpected relationships - for instance, a branch could be a joint venture between two departments.