Database schema for Categories

[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)
description

What 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 etc

So 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                                           yes

That way I'll keep all categories together and could avoid inserting the same category many times.

[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

[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.

[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

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?

[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

[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

[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');