Table structure for product catalogs from many manufacturers
I am building an online store using OS Commerce, and I am looking to import a number of different manufacturers products into MySql. In total, there could be 1,000-3,000 products from approximately 25 manufacturers catalogs, and the characteristics for every manafacturer are different.
While the characteristics are different, there are some similarities; however, OS Commerce's database structure does not include most of the fields I would need, so I'll need to set-up new product tables to accommodate this information.
I want to allow purchasers to customize the items they are purchasing by selecting certain fields (quantity, price, color, style, etc.) from drop down menus. Additionally, a key factor is that they would also need to complete free-form fields to type text. Ideally, I'd like the free-form fields to dynamically appear based on the characteristics each manufacturer allows. For instance, one manufacturer may allow 3 lines of copy with 8 typefaces and another may allow 8 lines of copy with 26 typefaces. I need to be able to display these variables based on the manufacturer's specifications.
My questions follow:
Since every manufacturer has different field characteristics, should I create separate product tables that are specific to each manufacturer, should I combine them into a single product table, or does it matter?
How will the performance of the database be affected, if at all, by your response above?
Once the database structure has been set-up appropirately to accept this information, what tools are available to import the manufacturers' catalogs and photos into MySql from Excel?
Is there a tool that would allow me to easily and selectively update, modify, and delete discontinued product from the database?
Thanks in advance for your assistance.