Need help with DB Schema - Post with multiple Categories and SubCategories

[bprosic]

Hi, I want to design DB for posts with multiple subcategories and categories.
Example:
Category has information: C#, PHP, JS, Python.
Subcategory can contain: Logger, String, While Loop.
A post table contain posts about "while loop" in PHP language and posts about "while" loop in C#.
So, this is a many-to-many relation? Then I have to put middle table in between, but how?
Using MySql.
I designed a scheme, worked on stored procedures, php but I have to dismiss it all because I'm missing something.
I could delete subcategories and stick to only categories and use tags? That is simplier solution, but not professional.
So far:

tblCat
idCat (int, unique, autoinc)
name

tblSubCat
idSubCat (int, unique, autoinc)
name
idCat (fk - references tblCat)

tblPosts
idPost (int, unique, autoinc)
name
tblSubCat (fk - references tblSubCat)
tblCat (fk - references tblCat)

tblTags
idTag (int, unique, autoinc)
tagName

tblTagPostMap
idTag (fk - references tblTags)
idPost (fk - references tblPosts)

If there is an answer already, please, share the link. Thank you!!

[bprosic]

I think that I will delete a foreign key in subcategories table and foreign keys in posts table.
Then I will create another table postCatSubCatMap which will combine posts, categories, subcategories with foreign keys.

[AndrisP]

Actually you can create multi level categories in single table if data structures are similar with references to self table . Second table contains references many-to-many e.g.

create table `categories`(
    `id` int primary key not null auto_increment
    ,`title` varchar(30) not null
    ,`description` varchar(300) not null
    ,`op_datetime` timestamp default current_timestamp on update current_timestamp
);
create table `cat_refs`(
    `id` int primary key not null auto_increment
    ,`cat_id` int not null
    ,`parent_id` int not null
    ,`op_datetime` timestamp default current_timestamp on update current_timestamp
);
alter table `categories` add constraint `unique_title` unique key(`title`);
alter table `cat_refs` add constraint `unique_ref` unique key(`cat_id`,`parent_id`);
alter table `cat_refs` add constraint `ref_to_cat`
    foreign key(`cat_id`) references `categories`(`id`);
alter table `cat_refs` add constraint `ref_to_parent`
    foreign key(`parent_id`) references `categories`(`id`);

drop procedure if exists `make_reference`;
drop procedure if exists `drop_reference`;
delimiter $$
create procedure `make_reference`(
    in `cat_title` varchar(30)
    ,in `parent_title` varchar(30)
)
begin
    declare `v_cat_id` int;
    declare `v_parent_id` int;
    select t.`id` into `v_cat_id` from `categories` t where t.`title` = `cat_title`;
    select t.`id` into `v_parent_id` from `categories` t where t.`title` = `parent_title`;
    if `v_cat_id` is not null and `v_parent_id` is not null then
        -- ignore witout errors if reference exists
        insert ignore into `cat_refs`(`cat_id`,`parent_id`) values (`v_cat_id`,`v_parent_id`);
    end if;
end $$

create procedure `drop_reference`(
    in `cat_title` varchar(30)
    ,in `parent_title` varchar(30)
)
begin
    declare `v_cat_id` int;
    declare `v_parent_id` int;
    select t.`id` into `v_cat_id` from `categories` t where t.`title` = `cat_title`;
    select t.`id` into `v_parent_id` from `categories` t where t.`title` = `parent_title`;
    delete from `cat_refs` where `cat_id` = `v_cat_id` and `parent_id` = `v_parent_id`;
end $$
delimiter ;

insert into `categories`(`title`,`description`)
    values ('title-1','description-1')
    ,('title-2','description-2')
    ,('title-3','description-3')
    ,('title-4','description-4')
    ,('title-5','description-5')
    ,('title-6','description-6')
    ,('title-7','description-7')
    ,('title-8','description-8')
    ,('title-9','description-9');

In this example you can insert multilevel tree of categories and any subcategory may be linked to several parent categories.
e.g.
2, 5, 6 linked as childs to 1
3 linked as child to 2
4 linked as child to 3
but 7, 8, 9 linked as child to 4, 5, 6

    call `make_reference`('title-2','title-1');
    call `make_reference`('title-3','title-2');
    call `make_reference`('title-4','title-3');
    call `make_reference`('title-5','title-1');
    call `make_reference`('title-6','title-1');
    call `make_reference`('title-7','title-4');
    call `make_reference`('title-8','title-4');
    call `make_reference`('title-9','title-4');
    call `make_reference`('title-7','title-5');
    call `make_reference`('title-8','title-5');
    call `make_reference`('title-9','title-5');
    call `make_reference`('title-7','title-6');
    call `make_reference`('title-8','title-6');
    call `make_reference`('title-9','title-6');