Congratulations on your recent graduation and the development of your eCommerce Product Vendor app! Handling variations in a store is indeed a complex task, and your concern about the database structure is valid. Here's a suggestion for a database schema to address the requirements/constraints you've outlined:

Product Table:

id
title
price
category
vendor
sku
ean number

Store Table:

id
name

Variation Table:

id
product_id (foreign key to Product)
name
price
meta_data (JSON field or similar for custom meta data)

Product_Store Table (for non-variable products):

id
product_id (foreign key to Product)
store_id (foreign key to Store)
is_imported
shopify_id
custom_description
custom_price

Product_Store_Variation Table (for variable products):

id
product_store_id (foreign key to Product_Store)
variation_id (foreign key to Variation)
is_imported
shopify_id
custom_name
custom_price

In this schema:

• The Variation table stores the variations for a product, including custom meta data.
• For non-variable products, you use the Product_Store table as you initially designed.
• For variable products, the Product_Store_Variation table handles each variation with its specific custom information.

This schema allows you to avoid redundancy while efficiently managing variations. You can use JOINs when necessary, but the structure should make it relatively straightforward to access the data you need.

Feel free to adjust this schema based on your specific application requirements, and I hope it provides a solid foundation for handling variable products in your app!