MySQL architecture (hierarchical data)

Basically the question is whether I should use the same columns in every table that uses hierarchical data or instead have one table with those columns that handles hierarchical data of all the types of data.

My database stores different types of hierarchical data: Pages, Questions, etc. Below is given the questions database.

CREATE TABLE `hp_questions` (
  `client_id` int(4) unsigned NOT NULL,
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `root_id` int(4) unsigned NOT NULL,
  `parent_id` int(4) unsigned NOT NULL,
  `depth` int(4) unsigned NOT NULL DEFAULT '0',
  `position` int(4) unsigned NOT NULL DEFAULT '0',
  `absolute_position` int(4) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `body` longtext COLLATE utf8_unicode_ci,
  `last_modified_by_id` int(4) unsigned DEFAULT NULL,
  `last_modified_on` int(4) unsigned DEFAULT NULL,
  `language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=738 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, root_id, parent_id, depth, position, absolute_position are used only for hierarchical data. The s开发者_如何学Pythoname columns has pages, filesystem, templates and permissions tables. I was wondering whether it would be more correct to put them into one table instead and add additional column type indicating the type of data?

The 'correct' answer depends strongly on what you are trying to do and how the data is presented, but I'd say yes, your suggestion makes sense. If the hierarchy is at the core of the data organization, then separating it out makes sense as it normalizes your data.

Doing so would require that you add additional logic in code to ensure that you link to the correct questions/pages/filesystems/etc tables depending on what you are looking at.