I'm getting some an issue with establishing a mysql table which will hold a listing of groups and subcategories. I'm not to sure how you can setup the table. Must it be 2 separate tables? 1 for that primary groups and 1 for that subcategories or will it be all in 1 table? Would something similar to the 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 the work or perhaps is this drastically wrong? Thanks ahead of time for that help!
It is dependent.
Are groups and subcategories really two various things? What this means is groups don't have any parent, while subcategories will always be inside a parent category and also have no further subs of themselves. Then two tables is alright.
Whether it's just like a tree though, where you will find just groups, which could both be children and also have children, you need to use one table (Google "nested set").
(Or you don't mean category/subcategory but primary category/secondary category, in which the secondary category isn't fixed to some certain primary category. Electronics + Cycling rather than Cycling->Speedometers. Then you may play one table whether it may be Cycling + Electronics)
If you're 100% certain you'll have only two amounts of groups (primary and sub), you are able to perform a couple of various things. Not one of them are the suggested 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 automobiles?
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 would like something within the "vehicle" category, although not most of the subcategories of verhicles, simply have a category record where primary='vehicles' with sub NULL.
Obviously, this isn't particularly flexible. You are tied to just two amounts of categorization, and there is very little business logic baked into your category model. But it may be sufficient to your requirements.
Two other good, proven, models would be the adjacency list model, and also the nested set model, each of which are referred to, with a lot of nice example mysql code, over here