database - SQL: How to implement generic categories? -

I need to implement the following classification in SQL:

• Multiple tests in the group Or Multiple
• Testing can occur in many groups
• (Children) Groups may be in multiple (parent) groups

  .. Which means 2x: N relationships. The following table groups can store trees:

  TEST_TABLE
  test_id (pk), test_name

  GROUP_TABLE < Group_id (pk), group_name

  TEST_IN_GROUP_TABLE
  test_id (fk), group_id (fk)

  GROUP_RELATIONS_TABLE
  Parent_group_id (fk), child_group_id (fk)

  but:

  • How to list all the tests in the group? How modify tables me impossibly difficult to find, because the group can be tested or sub-group with other tests
  • or choose it? I can make an additional child_count column in GROUP_TABLE, it will be filled automatically by the trigger but with this it is also hard for me.

    If you used the general table expression first group, then using that A group of groups, creating group_ids to be included on the test table through TEST_IN_GROUP_TABLE for all tests

      ;. When as select group_id from (GROUP_TABLE with CTE where group_id = 1 union select all GROUP_RELATIONS_TABLE inner child_group_id include CTE tg.group_id = cte.group_id inner TEST_TABLE not to join the inner TEST_IN_GROUP_TABLE TG t.test_id = Tg.test_id