I think JOIN is supported by all engines; FOREIGN KEYS are indeed not supported by MyISAM. However, I only mentioned for completeness that I used MyISAM for the simple test case. I do think InnoDB doesn't require auto_increment columns to be unique either.
Originally Posted by IBazz
Anyway, I am thinking now of the following set-up:
CREATE TABLE trades (
tradeid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
stockid INTEGER UNSIGNED,
price DECIMAL(10, 4),
quantity INTEGER UNSIGNED,
version INTEGER UNSIGNED,
- for a new trade, the tradeid will be auto-generated, status 'Live', and version 1.
- if the trade gets changed, the orginal record will be updated to status 'Changed' and statusdt gets a new timestamp. Then, a new record will be added with the orginal tradeid, status 'Live', and version 2.
- if the trade then gets cancelled, the status of the 'Live' trade (= latest version) will be updated to 'Cancelled' and statusdt will get a new timestamp
So, the combination of tradeid and version will be unique (but there can be several records corresponding to one tradeid).
I am not completely sure what database logging is. But I could imagine one would like to view the audit trail (= different versions) within the user application. Would that be possible with database logging?
Originally Posted by felgall