Ttdfjt

TLDR: Migrating Opencart to Magento, currently have 300+sku's and 100k+ product pages. What is the best practice for storing complex product data and programmatically adding and maintaining it directly to the database? What tables need to be updated, in what order and how best should I structure the product data.

The Detail:

Hello everyone in Magento community.

I am new to Magento and I am hoping through this post, user can point me in the direction of the best reading resources to answer some of my broad questions. Also, any specifics on how to tackle the solution would be very much welcomed.

So to the business problem. I am currently running Version 1.5.5.1 of Opencart, with a store of probably around 150,000 products.

Simply put, I am happy with how the store is operating, but due to the version, I am forced with either migrating to later version of Opencart, moving to Magento or rewiring my old store to work with newer versions of PHP.

Here - I'm looking for advice on migrating to Magento and how best to approach it.

Currently, my store is uniquely set up, as the product database is maintained by the backend c# system I've written myself. This is because managing the volume of products I have is just not tenable in Opencart.

Of particular importance, is conceptually for every one product in my local system, it generates 100's if not 1000's of product variations that get loaded onto Opencart.

To explain further, let's take a simple analogy of Car headlamp bulb. My site would advertise as sell:

Headlamp Bulb Ford Focus St
Headlamp Bulb Ford Focus Hatchback (2011 - 2018)
Headlamp Bulb Ford Focus Hatchback (2005 - 2011)
Headlamp Bulb Ford Focus RS (2009 - 2010)
Headlamp Bulb Ford Focus Estate (2011 - 2018)
etc etc

So for each make, model, variation the bulb fits, there would be a logical product on my site with a unique URL. My site doesn't sell headlamp bulbs, but this is just for example purposes.

The single product on my C# backend has an array of all the appropriate models a part will fit, then generates all the data for a website by interacting directly with the Opencart database via SQL.

I am now looking at migrating this process to Magento. With this I have the following questions:

What is the best way to logically represent the products in Magento and still have the "Headlamp Bulb Ford Focus Hatchback (2005 - 2011)" breakdown in the product title. Is it to have 1000's of simple products or is there a better approach using titles combined with product attributes?

If I go with 1000's simple products route, what are the common performance pitfuls I should be aware of to work around? For example in Opencart, I restricted it calculating the number of products in a category, heavily customised the search function and introduced custom page caching and optimised all the table indices to support large numbers of products being searched.

What is the best way to add new products to programmatically? Opencart I was working directly with the data tables, this was fast and didn't tie up the server too long when making large changes.

How do I manage logical inventory? With Multiple products representing one inventory item? "The Bulb", what is the best approach.

I am going to be needing to migrate customer data and orders - but I want to see if the inventory system is up to scratch first for what I need.

I am happy with taking on this work, doing the reading and legwork, but if people are able to point me in direction of I wish I read this first or understood this first from there experience of working with Magento.

So far my research I've found:
TABLES that are updated when creating a product
What are the main tables that updated when we creating new product in magento?

Very useful Table Entity-Relationship diagram for Magento:
Magento 2: Diagram of database tables, such as Orders, Products, etc. and their relationships

Many thanks!