Topic: Best way to create my database

[zimmo]

I am a new learner when it comes to mysql and creating a product database for an online shop. I am not sure which is the correct way to build the tables.

This is the scenario:

1: So far I have created the product category database, there are 33 product categories here and I have set this up as follows:

DB: Mysql
Table has 2 fields: cat_id and category name.
I have then inserted 33 rows (the categories)

I then need to create the actual product database which will consist of id, title, description and price. But I am unsure how to relate the product to a category? Should I create a third table for this?

I am just trying to get my set up correct.

Any help appreciated.

[kickstart]

Hi

Will a product only EVER belong to one category. If so then you could put the category id in the product table.

However I expect that a product can be belong to multiple categories, in which case add a link table linking the 2 tables toegther with one row for each combination of product and category.

All the best

Keith

[zimmo]

Thanks for the reply. Yes there are going to be multiple categories for the products. So I create my category table, then create the product table and then I create a join table between the 2.

How would I perform an insert into the join table? For example say the client adds a new product, and they have 10 categories to choose, and they select them all, how would you insert that data into the table?

Many thanks

[kickstart]

Hi

I would assume that the selected categories are returned as id fields when the user selects check boxes for them.

Insert the new product to the main table and get the id of the product (ie use mysql_insert_id. Validate the ids of the selected categories and insert a record for each one.

Quickly knocked up, something like this:-


<?php

$ProductName = mysql_real_escape_string($_REQUEST['ProductName']);
$ProductPrice = mysql_real_escape_string($_REQUEST['ProductPrice']);
$ProductSelectedCategories = array();
foreach($_REQUEST['SelectedCategories'] AS $CategoryId)
{

if (is_numeric($CategoryId)) $ProductSelectedCategories[] = $CategoryId;
}

$sql = "INSERT INTO Products (Id, ProductName, ProductPrice) VALUES(NULL, $ProductName, $ProductPrice)";

$sql = mysql_query($sql) or die(mysql_error());

$RetId = mysql_insert_id();

$InsertClauses = array();
foreach($ProductSelectedCategories AS $ProductCategory)
{

$InsertClauses[] = "(NULL, $RetId, $ProductCategory)";
}
$sql = "INSERT INTO ProductsCategoriesJoin (Id, ProductId, CategoryId) VALUES ".implode(',',$InsertClauses);

$sql = mysql_query($sql) or die(mysql_error());

?>


Of course you would want to do more validation (ie, check there are any selected categories, possibly check the selected categories are valid for the current user, etc).

All the best

Keith

[zimmo]

Wonder if anyone can help me create my join table and what I need to modify for the other tables. Here are the current table schemas, i have just done a dump of each.

Here is my category table:

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL auto_increment,
  `category` varchar(128) NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 AUTO_INCREMENT=34 ;


Here is my product table:
CREATE TABLE `products` (
  `prod_id` int(11) NOT NULL,
  `product_title` varchar(255) NOT NULL,
  `product_description` tinytext NOT NULL,
  `standard_price` decimal(10,2) NOT NULL default '0.00',
  `deluxe_price` decimal(10,2) NOT NULL default '0.00'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


I am unsure how I create the third table to associate the products with the relevant categories.

[zimmo]

Would I create a table like this for the products and categories link:

CREATE TABLE `prodcat` (
  `prod_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

But I am not sure if I need to set a unique identifier

[kickstart]

Hi

You could use a 2 part unique key, but personally I would just use an auto increment primary key with indexes on each of prod_id and category_id.

All the best

Keith