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?:
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
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.
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.
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.
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.