not sure if I'm in the correct forums, but what I wanted to ask is how would you implement a hierarchies in database terms?
for example, a person belongs to a service, and then to a team, and then to a section, and then to a channel, and then to a division.
so to summarize, a person will have these values associated to him/her:
service > team > group > domain > company.
I think the better approach is to have different tables for each category, so there will be 5 tables for the categories and 1 table for the person entity that will hold the relationship to the appropriate categories to a person. so the person table will have the columns serviceId, teamId, groupId, domainId, companyId which are all FK to the category tables.
or do you think this approach is better. have all the categories be placed in one table, and be differentiated by a TYPE field which will identify what type of category it is. also the hierarchy needs to be declared as well. so there would a level column that will identify which is the parent of which category.
which design do you think is better, in terms of optimization, operationability and complexity?
Best Answer
If your hierarchy is rigid (i.e. the levels are consistent and mandatory) then there is nothing wrong with representing each level as it's own table. This is your first option, except that instead of having foreign keys on the person table to each of the organization levels, you should have a single foreign key on the person table to the leaf level of your organization, and then each level in turn refers to it's parent/container. i.e.:
Person=>Service=>Team=>Group=>Domain=>CompanyWith this modification, your tables will be in third normal form (3NF) which is always a good starting point from a design perspective.
If, on the other hand, your hierarchy might be at all fluid, then keeping a hierarchy in a single table with a self-referencing (involuted) foreign key is a better option from a code stability perspective. This is your second option.
One issue with this second option is that relational databases are not brilliant at handling unleveled trees in this form. There are, however, a couple of pretty useful techniques for managing this type of data in this format a little more easily. You can Google around for adjacency lists and visitation numbers to learn more.