CRUD with a mapping table
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I want users to be able to submit new categories, subcategories, and sub-subcategories.. These categories, subcategories, and sub-subcategories are displayed in cascading dropdowns (I already have the code for that). I want a many-to-many relation between them using a separate mapping table. I want users to be able to complete all CRUD operations based on clicking buttons acting on the item that is selected in the dropdown. There can be multiple buttons for each dropdown. The code should check for redundant entries. Willing to tip extra if the solution really fits my needs!

Crowdsource coding tasks.

1 Solution


If you have some level of depth your categories table.
Like categories -> subcategories -> sub-subcategories -> sub sub-subcategories
You can store everything in one table

CREATE TABLE IF NOT EXISTS categories (
categoryId int(10) unsigned NOT NULL AUTO_INCREMENT,
categoryLevel int(11) NOT NULL,
categoryName varchar(250) COLLATE utf8_unicode_ci NOT NULL,
categoryParentId int(11) NOT NULL,
leafCategory tinyint(1) NOT NULL,
PRIMARY KEY (categoryId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

For those categories without parent will have categoryId = categoryParentId
For those categories with parent will have parent categoryId = categoryParentId
For those categories with child will have leafCategory = true