Help with MySQL table design

unhappyBB

Hi all, I need to create a table that will be used to store the volume of all products produced per hour. There will be millions of records so storage requirements and speed of querying has me worried enough to ask for your advice before setting up.

My initial instinct was to use one table with a multiple column index of (date,type,prod):
(I've never used indexes before, reading up on them now)

Datetime         | Type | Prod | Volume
2020-02-01 07:00 | A01  | K123 |   7
2020-02-01 07:00 | A01  | B123 |  17
2020-02-01 07:00 | A02  | B123 |  57
2020-02-01 08:00 | A02  | G321 |   3

But as there will be up to 100k rows added per day but only ~1k unique products would I be better off creating a Product table and replacing the 2 text columns above with a Product_id(int) and using a Join when querying?
I know this would help in the storage department but not sure if it would speed up or slow down querying the table. How would I go about indexing this? (I've heard of but haven't even looked into foreign keys yet)

Table 1:               Table 2:
ID | Type | Prod       Datetime         | Prod_id | Volume
 1 | A01  | K123       2020-02-01 07:00 |   1     |   7
 2 | A01  | B123       2020-02-01 07:00 |   2     |  17
 3 | A02  | B123       2020-02-01 07:00 |   3     |  57
 4 | A02  | G321       2020-02-01 07:00 |   4     |   3

Jim2007

Generally speaking you should separate transactional data from reporting data. For transactional data: only an auto generate primary key, no foreign keys, composite indexes, unique conmtratints etc... just write the data and get out as quickly as possible.

A second process is used to process this data and write it to a table in a manner that is suitable for business consumption.

A 100k rows is very manageable.