How to create a relation between mysql two tables?

Databases
1

I would like to create more categories. At first I thought to just add more fields to my existing table but I’m not so sure that is the best way, because I want to add a lot of categories.

So I thought creating a new Categories table might be best. After reading up a little on it, it seems I would have to change ENGINE=MyISAM to InnoDB type. It seems I read somewhere that this might have some negative affects. Is that true?

Also, to link the two tables together I would need some kind of key. Do you think this would work with the table below?:

ProductID | Category1 | Category2 | Category3 | Category4 | Category5 |

Using the ProductID as the key? Is this the simplest way?

CREATE TABLE `naturehills` (
 `ProductID` INT(11) NOT NULL DEFAULT '0',
 `Name` VARCHAR(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `MerchantID` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Merchant` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Link` TEXT COLLATE latin1_general_ci NOT NULL,
 `Thumbnail` TEXT COLLATE latin1_general_ci NOT NULL,
 `BigImage` TEXT COLLATE latin1_general_ci NOT NULL,
 `Price` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `RetailPrice` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Category` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `SubCategory` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `Description` LONGTEXT COLLATE latin1_general_ci NOT NULL,
 `Custom1` TEXT COLLATE latin1_general_ci NOT NULL,
 `Custom2` TEXT COLLATE latin1_general_ci NOT NULL,
 `Custom3` TEXT COLLATE latin1_general_ci NOT NULL,
 `Custom4` TEXT COLLATE latin1_general_ci NOT NULL,
 `Custom5` TEXT COLLATE latin1_general_ci NOT NULL,
 `LastUpdated` VARCHAR(100) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 `status` VARCHAR(50) COLLATE latin1_general_ci NOT NULL DEFAULT '',
 PRIMARY KEY  (`ProductID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Thanks

2

question!

1 products can be in different categories right?

3

Yes, one product can be in many categories.

Its mostly for a category navigation. Like some kind of category tree or drop down menu with subcategories. Plus maybe a person could site search in certain categories.

4

In that case your suggestion is not the way to go. But I now hear you mentioning subcategories as well. I think you should try to figure out what it is you want before you start to create all kind of tables to find out later that actually another table is needed or that the relationship between tables are not how they are suppose to be

5

If categories will be able to also have subcategories, I strongly recommend you use the Nested Set Model for your categories table.

it made life a lot easier for me when moving and/or deleting categories from the categories hierarchy.

if a product can be in more than 1 category then I would do something like this

tblCategories

fldCatId
fldCatName
…

tblProducts

fldProdId
fldProdName
…

tblProdCats

fldId
fldProdId
fldCatId

if using MyISAM tables then you must do your FK validations in your application code (php or whatever) before inserting records in tblProdCats

6

I know what I want but I’m trying to take this in small bites.:slight_smile: I want Categories, SubCategories, and Keywords for each product.

I thought I might be able to start with Categories. Maybe create a table for each one.

Maybe one table like this:

ID | Trees | Flowers | etc…

then assign numbers for each category

Like Trees = 1
Flowers = 2

Then in the main product table in the category column put the number(s) that product is in.

I don’t know

But for sure I need at least Categories that’s a minimum.

7

In that case the way described by Kalon is the way to go. Make the relationship using the third table and you can obviously do the same later for the subcategories

8

you have 2 options here the way I see it.

  1. use a 3 table model similar to what I suggested and I suspect most would suggest.

or

  1. just have 2 tables - 1 for categories and 1 for products with the products table having a column which is a list of category id’s the product is in.

Imho, option 2 is a huge :disagree: no-no :disagree:

Either way, have a look at the nested set model for your categories table.

9

As he said himself a product can be in many categories so to have a category_id field in your products table doesn’t make any sense. No the three tables is the way to go

10

If I use MyISAM when the table updates it wont update the categories table at the same time, will it?

11

I would only suggest MyISAM if you would like to use fulltext search, and since tblProdCats is a relational table it should be innodb

12

I think there are database performance differences between MyISAM and innoDB which I suppose why, at least historically, most web host providers supported MyISAM. I don’t know if that is still the case.

Anyway, especially if using MyISAM, before running the query to insert a record in tblProdCats, run a query that returns the number of rows in tblProducts containing the prodId you are about to insert.

That must return a value of 1, indicating the prodId already exists. If it returns 0, then the prodId doesn’t exist yet and so do not proceed with inserting the record in tblProdCats.

ditto for checking if the catId you are about to insert also exists in tblCategories.

the whole idea of this is to maintain data integrity by not inserting records in tblProdCats for products and/or categories that do not already exist in tblProducts and tblCategories.

13

this approach is twice as inefficient as it needs to be

first of all, it is susceptible to a race condition, unless you wrap the SELECT followed by the INSERT in a transaction, but doing that makes it even more inefficient

the simpler way is just to use INSERT IGNORE

if it didn’t already exist, it will exist afterwards, and if it did already exist, then it will still exist after the IGNORE

easy peasy :slight_smile:

14

I already have a php class method that runs separate select queries to see if the prodId and catId exist in the products and categories tables before it runs the separate insert query. The first method returns true or false and the insert is run only if the previous method returns true.

that’s as simple as it can get for me.

15

i just explained how you can do it in a single query instead of two queries

stick to your way, by all means, if you want to run at 100% inefficient overhead

16

ok thanks I will.

changing code that works and isn’t causing me any problems is not high on my priority list of things to do especially when any increase in time efficiency is probably in the order of milli if not micro-seconds or less.

17

that’s fine, and i agree with you, it would take extra effort to go back and change code that’s working, even if the code is inefficient

i merely wanted to post into this thread for the benefit of others, besides yourself, to let them know that there is a better way to achieve the same result

:slight_smile:

18

ok no problem :slight_smile:

19

i’m glad you finally agree with me for once :slight_smile:

20

yeah, wonders never cease eh? :slight_smile:

it’s just that even if running 2 queries is 100% slower than running 1 query, when the execution time of 1 query is so tiny, doubling the execution time results in it still being far too tiny for me to be concerned about.