What's the best way to store all these data in db?

197
March 17, 2017, at 00:25 AM

My client has given me about 14k urls of various products and he wants me to store all the price changes of that product per day. I think it will require an immense amount of db storage and a lot of optimization. I've never done this before. I'm using mysql DB. Should I store all these price changes per product in a JSON column or as separate row? Looking for tips regarding this. Thanks!

Answer 1

JSON columns are not as efficient as normal SQL columns and should be reserved for when you're not sure what data you're going to have. You're pretty sure what data you're going to have.

This is a pretty straightforward two table schema. One table for the product, and one for its price changes.

create table product (
    id integer primary key auto_increment,
    name varchar,
    url varchar unique,
    ...any other information about the product you might want to store...
    index(url)
);

By giving it a primary key it shields you from the URL changing, and it reduces the amount that must be stored in tables that refer to it. They only have to store the integer primary key, not the whole URL. The URL is indexed for faster searches.

Now that you have a product table other tables can refer to it. Like a table of price changes.

create table product_price_changes (
    product_id integer references product(id),
    price numeric(9,2) not null,
    change_time datetime not null,
    index(change_time)
);

This table stores when the price for a product changes, and what that price is. This is how you attach lists of data to things in SQL. The change_time is indexed for faster searches.

A simple join lets you efficiently see all the changes to a particular product in order.

select price, change_time
from product_price_changes ppc
join product prod on ppc.product_id = prod.id
where prod.url = ?
order by change_time
READ ALSO
Outputting only a certain number of rows from a Json array to a HTML table

Outputting only a certain number of rows from a Json array to a HTML table

This might be difficult to explain, I will try my best

226
How can I export a mySQL #temp table to a .csv file within script?

How can I export a mySQL #temp table to a .csv file within script?

I'd like to write a script that allows me to exportcsv files from 15-20 temporary tables I created, using a script instead of having to copy and paste in a separate

266
Where the Datediff should go in a query which joins 3 tables?

Where the Datediff should go in a query which joins 3 tables?

I've completed the query below and it works, until I put Datediff into it

159
how to speed up the process of adding records to a database?

how to speed up the process of adding records to a database?

i'm creating a kind of school management system

186