Categories to 3rd normal form DB spec

Hi all,

I worked with Zen cart for some while & delighted with its functionality. Recently I created a site which contained several thousand products with a category structure 5 deep & found the system was slow, now I intend to create a new 1 but it has potentially 500,000 products with a 4 or 5 deep category structure.
I do like the self populating multi drop down tabs add on which uses jquery to drill down into the categories however in my experience it would mean duplicated categories many times & therfore slow down the site...or maybe I am justing doing it wrong.

So using the vehicle scenario often used for demonstrating I will show how I think it would look.

Vehicle type (Main top level category)

Saloon
Hatchback
SUV
Van

Manufactures (2nd level)

Ferrari
Bentley
Aston Martin
GM
Ford

Part Type (3rd level)

Engine & drive
Braking & suspension
Bodywork
Trim

Products could go directly into 3rd level with attributes use for colour ect were applicable.

Now, 2 things happen here:

1st

For each vehicle type, I would need a manufacturer category
For each manufacturer I would need a part type category

So if I had 10 vehicle types I would need to duplicate the manufacturers categories x 10 & would need to duplicate the part type for each manufacture (example say 50)

So I would end up with 500 duplicate categories, this would not be 3rd normal form. I saring using ExcelVBA to create the structure like this but it does not seem right having so many duplicated nested categories.

2nd

There is of course a manufacturer table in Zen Cart but if I don't use it at the top tier, like my example above then I don't think it would work without some restructuring espescially if I want self populating drop down menus.

So assuming (that's a big assumption) my category structure is correct & the only way of doing it using categories, then I was going to add to add tables similar to manufactures but for vehicle type & part type. This would need a file similar to product product music extra to tie the new table together. There could be even tables for part, as most parts will be the same for each vehicle. Of course SQL data would need to be modified to collate data for viewing & new jquery would be required to self populate each drop down from new tables ect ect.

So eventually my question......Is there a better way or, or am I missing something with the original category structure? Maybe even product types is the answer, hence why I am asking before carrying out what appears to be a large re jig of Zen Cart without justification.

Any help appreciated with thanks

Well it's certainly not the normal question, 12 hours later I Google "Zen Cart 3rd normal" and it's top of the tree. Does this mean ecommerce platforms ignore this?

I know I don't have any formal db or spreadsheet training, but I've never heard or seen the term "3rd normal form" in six years frequenting the Zen Cart forums. Those who don't have whatever education or experience you have will not know how to answer you.

Thanks for response GJH42.

Maybe I've just watched 1 to many tutorial videos. I don't have loads of experience on DB's either however I do understand the principle of 3NF, being little or no repeating data. My knowledge (albeit little) started from using Zen Cart & working with the DB directly with MySQL & then through Navicat.

I think you have answered my question though, the principle in Zen Cart & most other ecommerce sites as I understand it, is categories & sub categories which the way I compile them have continuos repeating data.
If I have car & SUV master categories & manufacturer of Ford in both of them as sub categories in does not comply with 3NF. I really as much as anything else just asking for confirmation of category structure. You experience with Zen Cart far outweighs mine so you opinion would be appreciated.

You are confusing a background "database structure" (3nf) with
How you "display results to customers", frontend, (categories and subcategories)

The category structure where you can "drill down" in different ways may appear duplicated.
But it should be using "linked products" NOT duplicated products.

flashy,
you're right insomuch as what you're talking about doesn't work with the category architecture ...... because what you're talking about really isn't categories at all.

I think that if you view what you describe as 3NF simply as a way of filtering by a set of (custom added) fields, you'll be far closer to your desired end result with minimal effort.

You already described what is needed for what you seek. I'd summarize it kinda like this:
a) add the necessary custom fields to each product
b) perhaps also normalize those data pieces out into one-to-many relationships with another table (such as how the "manufacturers_id" is already)
c) provide your visitors with a tool to filter their viewing by choosing which combinations of those pieces they require
d) then categorize your products separately, in a broader sense, perhaps completely unrelated to the 3 things you referred to as categories in your illustration.

gilby:

You are confusing a background "database structure" (3nf) with
How you "display results to customers", frontend, (categories and subcategories)

The category structure where you can "drill down" in different ways may appear duplicated.
But it should be using "linked products" NOT duplicated products.

thanks for response Gilby,

I delighted to have your input as this category problem has been in my head for so long.

Not sure what you mean by back end & front end though, well I think I do but they are to some extent one of the same. If I set up the categories it in the categories table within DB (back end) this allows the categories tab to be drilled down to a specific category for products (front end).
The link products in the main would not be duplicated however, the sub categories would be. I will try to explain what I mean:

As per my original scenario...

Vehicle types - top category
Manufactures - sub category.

Now manufactures would in many (but not all) repeat in top categories, the parts within the category may not be duplicates, would your suggestion this would still work? As I understood the link products would be the same item existing in 2 or more categories & this could actually happen too. For example, an alternator for a Toyota x model may may be manufactured by Bosch & would also fit Volvo x model, this would be a linked product existing in 1 or more categories.

I feel you are giving me the answer but the penny has not dropped, hence why I asked the question in the 1st place.

DrByte has also responded with an approach which I will answer too.

Hopefully once this thread is redundant, I will have a better understanding.

Thanks for you input DrByte, I flattered the "Rocket scientist" of Zen Cart is offering advice.

Your suggestion sounds simple enough although I am a little confused on part D, but that aside for now.

Would you agree by duplicating sub cats & sub sub cats the site would be slowed down dramatically or am I wasting my time. I not worried about 3NF if the site functions okay but feel it would effected enough to give a poor customer experience. I am asking as everyone else using Zen Cart or similar is happy to work in it's default state. Maybe it's just the sheer size this could turn out to be...500,000 products plus with hundreds if not thousands of cats & sub cats ect.
Structuring the categories would be simple enough in Excel using VBA so that's not a problem.

So my question ultimately would be, which approach would you take?

You keep talking about this car parts "example" as though it's a theoretical example.

But ... is this "example" your actual business case?

No but the principle is exactly the same, can't say too much at this delicate early stage lol

In the "example", the business is selling "car parts". The other "parent" categories you mentioned are more like departments, or (as you mentioned initially) music genres or albums.
Normalizing your data akin to the music product type would accomplish most of what you seek.

Then you can add controls to allow customers to begin their navigation from any of those starting points, instead of only from the built-in "categories" navigation.

manufacturers = manufacturers, or artists
car types = genres
part types = categories, which contain the actual products you sell

Thanks,

Will try that & let you know, and it was worth me asking, the music clone is much better than just category system.