Hey folks, I graduated recently and my project was an eCommerce Product Vendor app. Basically, the app has hundreds of products from multiple vendors - and dropshippers can import those products into their Shopify stores. The most important feature of this app is that Dropshippers were able to generate custom AI content with a click of a button for each of the Products they imported in their Store.
So the schema looked like:
Product - id, title, price, category, vendor, sku, ean number, ...
Store - id, name, ...
Product_Store - product_id, store_id, shopify_id (nullable), custom_description, custom_price... .
Relations:
- a Product belongs to many Stores
- a Store has many Products.
So this was not really hard to implement, because it only required one pivot table that contained the unique content for each Product and Store combination. The tricky part is when I introduce Variations in the situation:
How would you define the schema to handle the following requirements/constraints:
- When a Store imports a variable Product, all Variations are imported by default in the Store.
- Users should be able to Exclude/Include which Variations they wants in their Store.
- Users should also be able to add custom prices/names/and other meta data for Variations in their store.
**Where would you store the meta data from the above constraints? **
The only solution that comes up to my mind is to have a 3-way relationship between Store-Product-Variation, and after a variable Product with X variations is created - I should insert X rows in this table (one for each Variation). Now this table can hold the custom logic for each Store, such as custom_price, custom_name and etc.
So the table would look like:
Product_Store_Variation: id, product_id, store_id, variation_id, is_imported, shopify_id, custom_name, custom_price, ...
👆 This doesn't seem like a good solution to me since I need to write JOIN queries from multiple tables in order to access all the data I need.
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!
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast