How to create child table related to single row in mysql?-CodePudding

I have table of thousands of market assets and want to store prices for every asset. New price will be added every minute. I was thinking how to desine this and figured out that the best way will be to create multiple tables - one for every asset.

What is the best way to make relation bettween asset in parent table and child table. I'm new in mysql so first thing I came up with was to make name of child table from PRIMARY KEY. But I don't think it's good practise, because I imagine that searching for that table will be very slow.

I'm using Python.

CodePudding user response：

The asset table should have a unique key (e.g. some type of integer). Then a single price table should have that key as a reference to the asset. Getting prices for an asset will involves a join of the asset and price table on the asset key.

CodePudding user response：

Probably a single table with

asset_id
datetime
price
PRIMARY KEY(asset_id, datetime)

This clusters all the prices for a given asset together, which will benefit things such as graphing.

Calculate how many rows such a table would have after a year. If it is a billion, we need to talk about a variety of optimization issues. Please provide the main queries that will be performed.