Tables design for big database with daily new data

Question

I have a big database with a table: products (having products.id as a primary key). I receive daily data about the performance of each product (Num of ordered items, revenue, product views…etc)

I’m wondering whether I should structure the database in a way that each product has its own table and each row represents a date

OR

Each date has a table then each row represents a product with it’s performance data

OR

Is there any other efficient way to it.

Thanks in advance !

Answer 1

One table, something like

CREATE TABLE mydata (
    the_day DATE NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    ket_profit ...,   -- maybe INT?  maybe FLOAT?
    conv_rate ...,    -- unless this is the quotient of two things
    kpi3 ...,   -- but use real name and datatype
    kpi4 ...,
    ...
    kpi10 ...,
    PRIMARY KEY(product_id, the_day)
) ENGINE=InnoDB;

That looks like less than a million rows a year -- a medium-sized table. The PK will make the query you sketched out run quite fast.