Suggestion: Add a CategoryId and ManufacturerId column to Product table

I have been working on a site that has over 411,000 products in 1400 categories by 1600 manufacturers. It would make  importing of this size of project a lot smoother if the product table had a CategoryId and a ManufacturerId column.

Since a product can be mapped to multiple categories, how should these extra columns help importing?

Having a product table with a category id field would mean that all product data (eg. name, descriptions, ...) need to duplicated for these categories and would easily lead to inconsistent data.

It should probably have something similar to the system that I am importing data from. It has a CategoryID, SubCategoryID, CategoryCode, FilterCode. For instance there is CategoryID 04 "Communications" with a SubCategoryID 0433 "Carrier Transport Equipment" and a CategoryCode MUX-CP "Multiplexor Components" which can then be sorted by seven different filter codes. There are 2.4 million Sku's of which about half of them are assigned to physical warehouse locations that rarely change but it is the tech industry and the products in those locations change quite often.

Understand your need for an efficient way to get all these products imported.
However, I do not yet understand how your suggestion to add these columns in the product table would help.
So in order to understand your exact needs...
Where would you like to use these columns? In the database, in a NopCommerce import file (imported via the admin portal), ... ?

If the current NopCommerce import functionality in the admin portal does not provide enough fields, you may have to implement your own import, or look for a 3rd party plugin that does that.

If you would like to import the data directly into the database, than there are ways to do that.
So far, it looks like the NopCommerce database has all tables available to hold the products the way you have them in the system you are importing from:
Products: Product
Categories/SubCategories: Category
Manufacturers: Manufacturer
Warehouses: Warehouse
Product - Category/SubCategory: Product_Category_Mapping
Product - Manufacturer: Product_Manufacturer_Mapping
Product - Warehouse: ProductWarehouseInventory

In order to get this amount of data into the system with any sort of efficiency I am having to import direct with SSMS. Which gets the products in the database but not mapped to any categories

I agree with you that most of the functionality that I mentioned of the other system are there. And the admin import works great and allows mapping of categories and manufacturers from a product spreadsheet. It is awfully time consuming with this amount of products.

Yes, syncing large amounts of data is something that needs te be done with care and may require time/effort/processing time.
Still, with some smart steps in your sql scripts, and maybe some extra (temporary) tables and/or views, it should be feasible and in a reasonable way.
If your import from the other system can be customized as well, that would make it probably easier as well.

The suggestion to add extra columns to the product table is another thing that will only introduce problems.

Yes, When I first suggested this I wasn't even thinking about the fact of having the same product mapped to multiple categories or even to multiple manufacturers. I have been setting up temp tables to import into and working out some scripts to make it manageable. Then maybe I can get some stored procedures in place to handle this.

Ok, that's a good way to go forward!
In case you need further help on that, just let it know somewhere in the NopCommerce forum (or by PM).

Best regards,
Micha Somers

Thanks for the feedback and assistance.

Steven