Timestamp primary & unique I need to create a database for data that looks like this:



I am planning to use this table structure:

CREATE TABLE IF NOT EXISTS `AUDCAD` (
  `date` BIGINT NOT NULL, 
  `open` decimal(7,5) DEFAULT NULL,
  `high` decimal(7,5) DEFAULT NULL,
  `low` decimal(7,5) DEFAULT NULL,
  `close` decimal(7,5) DEFAULT NULL,
  `volume` INT DEFAULT NULL,
  PRIMARY KEY(date),
  UNIQUE (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


date column is minute difference timestamps. It will be unique and if same timestamp insert/update came, can be ignored or override with no problem. 

This is my first time planning table structure. Does that sound have problem? Do you have a suggestion over this?
Without knowing the query set that's going to hit this structure, I'll give a generalized answer:

I try to use the TIMESTAMP type for this, which works well with time/date functions, and is timezone agnostic (under the hood it is storing a unix timestamp).

By default, this will only be a 4 byte wide key (instead of an 8 byte bigint), so if you index other columns (which always tacks on the primary key at the end...albeit hidden), those structures can stay skinny and quick as well...and also not eat your RAM ;-).

TIMESTAMP by default has a resolution of 1 second, which is overkill for your purposes, but the easy use of date functions on it makes that worth it.

Main downside: TIMESTAMP is based on a 32-bit unix timestamp integer (it stores that way, but displays as a date/time), so it does eventually run out. At that time (decades from now, when RAM is cheap) you can adjust it, and the date/time display/handling nature of the type will allow you to make the change transparently to your software.

If you want to use it as an integer, you can use FROM_UNIXTIME(your_integer_here) to insert, and UNIX_TIMESTAMP(date) in queries that need that. I'm going to guess though that the date/time format is easier to work with for you though.

Also, you do not require a second UNIQUE key, as any PRIMARY key implies this already, and is included for free in the storage/RAM price of your data table (which is sorted by PK as a B-Tree...your table is an index, and can/will be used as such).