MySQL Categories and Subcategories Table Structure

Related searches

I am having a little bit of a problem with setting up a mysql table that will hold a list of categories and subcategories. I am not to sure how to setup the table. Does it need to be 2 separate tables? 1 for the main categories and 1 for the subcategories or can it be all in 1 table? Would something like this work?

Create Table categories (
    category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sub_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (category_id)
)

CREATE TABLE items (
    item_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(100) NOT NULL,
    PRIMARY KEY (item_id),
    FOREIGN KEY (category_id) REFERENCES categories (category_id),
    FOREIGN KEY (sub_id) REFERENCES categories (sub_id)
)

Will this work or is this completely wrong? Thanks in advance for the help!


If you are 100% sure that you'll only have two levels of categories (main and sub), you can do a few different things. None of them are your proposed solution:

CREATE TABLE categories (
    id int not null primary key,
    main varchar(64)
    sub varchar(64)
);

CREATE TABLE objects (
    id int not null primary key,
    category_id int,
    name varchar(64),
    FOREIGN KEY (category_id) REFERENCES categories (id)
);

Want all vehicles?

SELECT * 
FROM objects AS o 
INNER JOIN categories AS c ON o.category_id = c.id 
WHERE c.main = 'vehicles';

Want all roflcopters?

SELECT * 
FROM objects AS o 
INNER JOIN categories AS c ON o.category_id = c.id 
WHERE c.main = 'vehicles' and c.sub='Roflcopters';

If you want something in the "vehicle" category, but not in any of the subcategories of verhicles, just have a category record where main='vehicles' with sub NULL.

Of course, this is not particularly flexible. You're stuck with just two levels of categorization, and there's not a lot of business logic embedded in your category model. But it might be sufficient for your needs.


Two other good, proven, models are the adjacency list model, and the nested set model, both of which are described, with lots of nice example mysql code, over here

Categories and Subcategories Table Structure - mysql, Best is a relative term, but we can certainly explore the trade-offs of the available solutions to storing hierarchical data structures in a relational� Mysql Category Subcategory Structure. Flexible, expandable sleeve provides abrasion protection with a high aesthetic appeal. Now you create 2 subcategories on table list_services. Whether you’re a seasoned PHP PHP PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.


It depends.

Are categories and subcategories really two different things? This means categories have no parent, while subcategories are always in a parent category and have no further subs of themselves. Then two tables is ok.

If it's like a tree though, where there are just categories, which can both be children and have children, you should use one table (Google "nested set").

(Or maybe you don't mean category/subcategory but primary category/secondary category, where the secondary category is not fixed to a certain primary category. Electronics + Cycling instead of Cycling->Speedometers. Then you could use one table if it could also be Cycling + Electronics)

MySQL categories and subcategories, Let's start with the schema: CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) NOT NULL DEFAULT� Products Table : Product ID,Product Name,SubCategory. Category Table : Category ID, Category Name. Subcategory Table : Subcategory ID, SubCategory Name, Category ID Is there is better way to structure my tables and fields for easy retrieval and updating of data? I would like to be able to search and display products by Category as well as


It is obviously work. But you may use separate tables.

create table categories 
(
    categoryId int not null,
    categoryName varchar(20) not null,
    primary key(categoryId)
);

create table subcategories 
(
    subcategoryId int not null,
    subcategoryName varchar(20) not null,
    parentId int not null, 
    primary key(subcategoryId),
    foreign key(categoryId) references categories(categoryId)
);


create tables items 
(
    item_id int unsigned not null auto_increment,
    name varchar(255) not null,
    description varchar(100) not null,
    primary key(item_id),
    foreign key(categoryId) references categories(categoryId),
    foreign key(subcategoryId) references subcategories(subcategoryId)
 )

MySQL DB structure with Main category and several nested sub , I would generalize the design, I.e. instead of a motorcycle_brand table, You would want a category_brand table, and instead of a motorcycle_model table, you� table structure. Tables are associated with the category_id field by a one-to-many relationship. Those. 1 category can have many items. The two-level hierarchy in the Category table. This defines the main categories and subcategories. Categories are those records in which parent_id = NULL. And subcategories, these are those records in which parent_id = to some id.


Table structure for Products, Categories and Subcategories, Currently I have these tables and fields: Products Table : Product ID,Product Name,SubCategory. Category Table : Category ID, Category Name. Example. If you want to see the schema information of your table, you can use one of the following: SHOW CREATE TABLE child; -- Option 1 CREATE TABLE `child` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullName` varchar(100) NOT NULL, `myParent` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `mommy_daddy` (`myParent`), CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) ON


Best way to make multiple categories item database structure, Each product has a category with many subcategories. Should I go with this idea or I should do it with more preemptive way by making Categories table like: categores1, http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql /. Working with categories and subcategories. Using the adjacency model to implement categories and subcategories can be reduced to two simple steps: manage the hierarchical data; display the hierarchical data; Managing the hierarchy is nothing special. Just look again at the table layout. There's a primary key column (id) and a foreign key


The category and their subcategory are easily separated by a tree structure. The categories tree view is always recommended to display an infinite level of categories and subcategories. In this tutorial, we will show you how to create dynamic category subcategory tree using PHP and MySQL.