Database Design

[Kasracer]

I need help designing my database for my knowledge base on my website. I need a way to store the following:

ID
Title
Body
Author
Primary Category
Sub Category
Pictures

Can you store pictures in a MySQL database and if so, how do you retreive them for use? Also, how should I setup the primary and sub categories? Just as text? Since I want this database searchable, should I make everything but ID and Pictures full text? I'm still new at this. I got a book but it sucks

[techwizz]

Here is the code to create a MySQL table named table1 .. i didnt know what to call the table.. lol

CREATE TABLE `table1` (
  `ID` int(6) unsigned NOT NULL auto_increment,
  `Title` text NOT NULL,
  `Body` text NOT NULL,
  `Authur` text NOT NULL,
  `PrimaryCat` text NOT NULL,
  `SubCat` text NOT NULL,
  `Picture` text NOT NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

MySQl doesnt store images but stores image locations or names as text. Then the php echos an image... and ask MySQL for the name. PHP builds the picture frame.. MySQL moves the frame so you can see the pic. That might be a dumb analogy... but hey... its Free. Lol

Here is possibly a better deffinition / explanation in this post link.

[visualAd]

I would say eliminate the primary category and sub category field from your table and put them in a separate table called kb_categories. You should also have an additional table for Categories too, so it will look something like this:

+-------------+
| kb_articles |
|=============|
| id {PK}     |-|------|
| title       |        |
| body        |        |       
| author      |        |
| pictures    |        | 
+-------------+        |
                       |
                       |
+------------------+   |
| kb_categories    |   |
|==================|   |
| aritcle_iD {PK}  |3--|    
| category_iD {PK} |3----|
+------------------+     |
                         |
                         | 
+---------------+        |
| categories    |        |
|===============|        |
| id            |-|------|
| category_name |
+---------------+

PK - denotes a primary key.

Instead of using the names we use a reference to the record in another table.

The reason I recommend this is that it does not constrain your articles to only two categories, each article can now belong to as many categories you like. You may want to keep the primary category field in you kb_articles table though, if you want each article to have a main category.

Another tip for MySql daabases, when naming fields and tables, always use lower case letters separated by underscores for each word as Mysql on linux is case sensitive.

P.s: I've moved this to the DB forum so some more expert eyes can have a look.

[The_Duck]

If you have a webserver might I suggest phpmyadmin (http://www.phpmyadmin.net/home_page/) - you will need php as well

This will make it much easier for you to create and edit your database tables

If you don't have a webserver I would recommend abyss web server (http://www.aprelium.com/) (Version x1)

Good Luck!

[Kasracer]

I understand how to create tables and such. I really needed to know like, do I make categories at text fields and then use a delimiter like a comma to seperate each one and parse in PHP or is there another way? I needed to get my database down so I could start coding my Knowledge base

[si_the_geek]

Using a delimited text field to contain references is a bad idea, as it will slow everything down dramatically. I would recommend using a table structure more along the lines that visualAd suggested, with all ID fields as numerics.

Instead of using the table kb_categories you could just add an extra field to the categories table for the sub-category name. eg:

kb_articles
  article_id {PK}     
  category_id {FK}
  title       
  body      
  author    
  pictures  

categories
  category_id {PK}
  category_name
  sub_category_name

This allows very little duplication of data (the category_name will be repeated for each sub-category that belongs to it). It does mean however that if you change the name of a category it needs to be updated for all sub-categories.

A design which eliminates duplication is this:

kb_articles
  article_id {PK}     
  sub_category_id {FK}
  title       
  body      
  author    
  pictures  

sub_categories
  sub_category_id {PK}
  category_id {FK}
  sub_category_name

categories
  category_id {PK}
  category_name

Which is better/faster is up to how you intend to use the data, but unless you have a reason not to I would recommend the second of these desgins.

[visualAd]

I described the other way, which is better in post #3. Using a delimiter to separate each category creates he following issues:

• Redundant / extra processing by the application (i.e: the PHP script) to separate and group categories.
• How do you check a category is valid?
• How would you rename a category?
• A type could result in to separate categories which are meant to be one category.