+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Database schema for Categories (/showthread.php?tid=61095)
Database schema for Categories - El Forum - 09-16-2014
[eluser]Lykos22[/eluser]
Hi, I'd like some feedback please, if possible. Lets take for example the following tables
Code:
Posts
-------------------------
post_id (pk)
title
category_id (fk)
post_body
Products
-----------------------
product_id (pk)
name
category_id (fk)
descriptionWhat I'm actually looking for is which is the best way to go for organizind my categories. I've thought of two solutions, however if you feel there's a better way to go I'd be happy if you let me know.
1. Create n-different category tables for each module (by module I mean posts, products, faq and anything in general that can be categorized) like
Code:
post_categories
------------------------------
category_id
name
product_categories
---------------------------------
category_id
name
etc etcSo every module will have its own category table.
2. Create a single categories table for storing the categories for all modules
Code:
categories
----------------------
category_id
name
use_in_posts (tinyint)
use_in_products (tinyint)So I could have the following
Code:
Category use in posts use in products
----------------------------------------------------------------------------------------------
design yes no
mobile phones yes yes
shoes no yesThat way I'll keep all categories together and could avoid inserting the same category many times.
Database schema for Categories - El Forum - 09-29-2014
[eluser]highendcode[/eluser]
a table structure is a representation of a relationship.
So by putting all the categories in one table what is the relationship that your are defining amongst the categories?
They are all in one table becuase???
and I suggest you put this in a mysql forum not here, this is not a databased focused forum
Database schema for Categories - El Forum - 09-29-2014
[eluser]InsiteFX[/eluser]
If the post pertains to CodeIgniter it does not matter what they ask.
The only thing I would add to it would be a sort_order field.
Database schema for Categories - El Forum - 09-30-2014
[eluser]Lykos22[/eluser]
@highendcode The code is on CodeIgniter, however its important to have the database structure correct in order to develop the code later, that's why I made this topic in 'General Discussion'.
@InsiteFX
Quote:The only thing I would add to it would be a sort_order field.What do you mean? I don't follow ... Do you reference in one of the 2 options I said before, or in an other (probably better) way of doing it?
Database schema for Categories - El Forum - 09-30-2014
[eluser]InsiteFX[/eluser]
Lets say I have a category like below.
Products sort_order
-- Game 1
-- Car 2
-- Phone 3
The sort_order field will allow me to sort them the way I want to display them.
Products sort_Order
-- Car 2
-- Phone 3
-- Game 1
Database schema for Categories - El Forum - 09-30-2014
[eluser]Lykos22[/eluser]
Oh I see what you mean. No, my focus is on which is the best way to categorize posts, products etc. how to organize them in Categories and which is the best way to organize my Categories
Database schema for Categories - El Forum - 09-30-2014
[eluser]InsiteFX[/eluser]
2 would be alright in your case.
Code:
-- --------------------------------------------------------
--
-- Table structure for table `categories`
--
CREATE TABLE `categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(255) NOT NULL DEFAULT '',
`cat_short_desc` varchar(255) NOT NULL DEFAULT '',
`cat_long_desc` text,
`cat_sort_order` int(3) NOT NULL DEFAULT '0',
`cat_parent_id` int(11) unsigned NOT NULL DEFAULT '0',
`cat_status` enum('active','inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` VALUES (1, 'Products', '', '', 0, 0, 'active');
INSERT INTO `categories` VALUES (2, 'Car', '', '', 0, 1, 'active');
INSERT INTO `categories` VALUES (3, 'Game', '', '', 0, 1, 'active');