I've got a v. simple table currently containing millions of data points of price on a given date:
CREATE TABLE data_point ( id INT PRIMARY KEY, symbol_id INT, x DATE, y DOUBLE )
This is a timeseries database and for this release I'll keep data with a daily (or less frequent) period but if I need to add a time component, would it be better to change the
data_point table and convert
If this did happen, initially all of the existing data would remain unchanged, it would not be adjusted - only future data (and not all of it) would be saved with time.
Or would it be better to add a second table with a one-to-one relationship to
data_point, which held the time data?
Performance is more critical than space.
This leads on to the associated question about this table. Which is better in terms of performance for huge amounts of data, when all the queries on
data_point will be joins from
INT for a primary key will get filled up within the app's expected lifetime)
CREATE TABLE symbol ( id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE data_point ( id BIGINT PRIMARY KEY, symbol_id INT, x DATE, y DOUBLE, CONSTRAINT FOREIGN KEY (symbol_id) REFERENCES symbol(id) );
CREATE TABLE data_point ( symbol_id INT, x DATE, y DOUBLE, CONSTRAINT PRIMARY KEY (symbol_id, x), CONSTRAINT FOREIGN KEY (symbol_id) REFERENCES symbol(id) );