What's a reliable database structure for managing e-commerce pricing and promotions?

Ecom
1

I’m working on an online store project and I’m stuck on how to set up the database. I need a way to handle product prices, discounts, taxes, and gift vouchers. Does anyone have experience with a good database structure for this?

I’ve tried a few different approaches, but they all seem to fall short when it comes to flexibility. I want to be able to easily apply discounts to specific products or categories, calculate taxes correctly, and allow customers to use gift vouchers.

Here’s what I’m thinking so far:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  base_price DECIMAL(10, 2)
);

CREATE TABLE discounts (
  id INT PRIMARY KEY,
  product_id INT,
  discount_percent DECIMAL(5, 2),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE gift_vouchers (
  id INT PRIMARY KEY,
  code VARCHAR(20),
  value DECIMAL(10, 2)
);

But I’m not sure if this is the best way to go about it. Any suggestions or best practices would be really helpful. Thanks!

2

Your current structure is a good starting point, but for a robust e-commerce system, consider expanding it. Add a ‘categories’ table to group products and apply category-wide discounts. Implement a ‘price_rules’ table to handle complex pricing logic, including time-based offers and quantity discounts. For tax management, create a separate ‘tax_rates’ table linked to products or categories. Lastly, consider a ‘customer_groups’ table to offer personalized pricing. These additions will significantly enhance your system’s flexibility and scalability. Remember to optimize your database queries and indexes for performance as your product catalog grows.

3

Hey there! Your database structure’s a good start, but have you thought about handling bundle deals? :thinking: I ran into that issue with my last project. Maybe add a ‘bundles’ table that links to multiple products? Could be super useful for those ‘buy 2, get 1 free’ type offers.

Also, what about seasonal pricing? Like, how do you plan to handle Black Friday madness? :sweat_smile: Maybe a ‘price_history’ table to track changes over time?

Oh, and here’s a random thought - loyalty points! Customers love those. Could be worth considering how to integrate that into your structure.

Just brainstorming here. What do you think? Have you dealt with any of these scenarios before?

4

hey grace, ur structure looks decent but u might wanna consider adding a promo_codes table for more flexibility. also, think about time-based discounts and bulk pricing. maybe add a categories table to apply discounts to whole groups. good luck with ur project!