MRT Server Behaviour with eCommerce db
I am building an admin for a future shopping cart. And it will contain products that will show up on more than one section.
Since the eCommerce database comes with a "productcategories" table I am using that for the main category. However there will be sub categories for each as well. I need this to be searchable.
Here's a fictional example of what I am trying to do;
Product #1 is a "Hammer"
Main Category would be "Tools"
Sub Categories would be "Hobby Tools" and "Professional Tools"
What I have so far is a drop down list in the insert form for the Categories that are populated by the "productcategories" table. This part works great.
Then I have a multi-select list for my Sub Categories. To which I then created two new tables. One is the "SubCategories" table and the second is the RelSubCategories" table that acts as the relational table for the MRT behavior.
The MRT writes the data into the new relational table including the ID from the "SubCategories" table and the ID from the "products" table just fine.
But how do I get the recordset for the products to include both tables for both a results list and a search form? Do I create a joins for this?
Or should the MRT be writing into a new column on the "products" table as well?
I'm very confused, so sorry if this made no sense.
For the record, I am trying to create something similar to the "Blue Sky Music" admin demo where it has both category and genre. But I wanted to use the newer eCommerce database instead since it seems to be ready for eCart.