CODEWITHSUNDEEP
sqldatabase
Scandi
Scandi

Reputation: 53

Price comparison database - put price data in main table, in one separate table or in many product tables?

I'm trying to build a price comparison database with n products and a definitive but changing number of vendors that sell these products.

For my price comparison database, I need to store both current prices for a product across different vendors and historical prices (one lowest price).

As I see it, I have 2 options to design the database tables:

1. Put all vendor prices into the main table.

I know how many vendors there will be and if I add or remove a vendor I can add or remove a column.

Historical prices (lowest price on certain date across all vendors), goes into a separate table with a product name, a price and a date.

2. Have one table for products and one table for prices

I will have only the static attribute data in the main table such as categories, attributes etc and then add prices to a separate product table where I store price, vendor, date in it and I can store the lowest price as a pseudo-vendor in that table for each date or I can store it in a separate table as well.

Which method would you suggest and am I missing something?

Upvotes: 2

Views: 1123

Answers (2)

yasinkuk
yasinkuk

Reputation: 63

Implementing Option 2 with a Database Schema Example

Here's a simplified schema:

Products Table: Stores static product data.

  • product_id (Primary Key)
  • product_name
  • category
  • attributes

Prices Table: Stores current and historical prices.

  • price_id (Primary Key)
  • product_id (Foreign Key)
  • vendor_id
  • price
  • date

Vendors Table:

  • vendor_id (Primary Key)
  • vendor_name
  • other_vendor_details

SQL Example for a Trigger to Record Lowest Historical Price

CREATE TABLE historical_prices (
    product_id INT,
    price DECIMAL(10, 2),
    date DATE,
    PRIMARY KEY (product_id, date)
);

DELIMITER $$

CREATE TRIGGER update_lowest_price
AFTER INSERT ON prices
FOR EACH ROW
BEGIN
    DECLARE lowest_price DECIMAL(10,2);
    -- Check for existing lowest price on the same date
    SELECT price INTO lowest_price FROM historical_prices
    WHERE product_id = NEW.product_id AND date = NEW.date;

    IF lowest_price IS NULL OR NEW.price < lowest_price THEN
        -- Insert or update the historical_prices table
        INSERT INTO historical_prices (product_id, price, date)
        VALUES (NEW.product_id, NEW.price, NEW.date)
        ON DUPLICATE KEY UPDATE price = LEAST(price, NEW.price);
    END IF;
END$$

DELIMITER ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You should store the base data in a normalized format that contains all the history. This means that you have tables for:

  • products, with one row per product and the static information about the products.
  • vendors, with one row per vendor and the static information about the vendor.
  • prices, with one row per price along with the date and product and vendor.

You can get the current and lowest prices using a query, such as:

select pr.*
from (select pr.*, min(price) over (partition by product) as min_price
             row_number() over (partition by product, vendor order by price_datetime desc) as seqnum
      from prices pr
      where pr.product_id = XXX
     ) pr
where seqnum = 1;

For performance, you want an index on prices(product, vendor, price_datetime desc).

Eventually, you may find that this query runs too slowly. In that case, you will then consider optimizations. One optimization would simply be storing the latest date for each price/vendor combination using a trigger, along with the minimum price in the products table -- presumably using triggers.

Another would be maintaining a summary table for each product and vendor using triggers. However, that is probably not how you should start the endeavor.

However, you might be surprised at how well the above query can perform on your data.

Upvotes: 1

Related Questions