CODEWITHSUNDEEP
databasedatabase-design
veljasije
veljasije

Reputation: 7092

Table with hierarchy or multiple tables?

I am designing database model for some application, and I have one table Post which belong to some category. OK, Category will logically be other table.

But, more categories belong to some super category or domain or area, and my question is next:

Whether create other table for super categories or domains, or to do this hierarchy in table Category with some combination of key to point to parent.

I hope I was clear with problem?

PS.I know that I can do this problem with both solution, but is there any benefits with using first over second solution, and contrary.

Thanks

Upvotes: 2

Views: 166

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476719

It depends: if nearly each category has a parent, you could add a parent serial as a column. Then your category table will look like

+--+----+------+
|ID|Name|Parent|
+--+----+------+

The problem with this representation is that, as long the hierarchy is not cyclic, some categories will have no parent. Furthermore a category can only have one parent.

Therefore I would suggest using a category_hierarchy table. An additional table:

+-----+------+
|Child|Parent|
+-----+------+

The disadvantage of this approach is that nearly each category will be repeated. And therefore if nearly all categories have parents, the redundancy will approximately scale with that number. If relations however are quite sparse, one saves space. Furthermore using an intelligent join will prevent the second representation from taking long execution times. You can for instance define a view to handle such requests.

Furthermore there are situations where the second approach can improve speed. For instance if you don't need the hierarchy all the time (for instance when mapping serials to the category-name), lookups in the category table can be faster, simply because the table is more compact and thus more parts of the table will be cached.

Upvotes: 3

Related Questions