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!!

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.

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');
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.