This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory.
We have a table where records get written to first, let's call it
tbl_recording which looks like:
CREATE TABLE tbl_recording (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) NULL,
registration VARCHAR(45) NULL,
legacyid VARCHAR(45) NOT NULL,
UNIQUE INDEX legacyid_UNIQUE (legacyid ASC),
PRIMARY KEY (id));
Then, we have yearly tables based off of
tbl_recording so like
tbl_recording_2013 etc etc, their structure is the same.
We also have
tbl_recording data off to the yearly table every hour (insert to yearly, delete from recording). Now, the problem I see is that the uniqueness of
legacyid is not remembered across all these tables. So, while we generate a hash for the legacyid, there could be a collision (even though terribly unlikely) at some point:
- After data is removed from
tbl_recordingthe same hash could be generated and re-entered into
tbl_recording, thus causing a blockage further down the line when trying to insert into the yearly table
Between years, the same hash could be generated for 2013 and 2014EDIT: This would never happen if i append the year to the hash
The best way I've come up with to mitigate against this is to keep a hashtable, one that stores all hashes created, and is checked against before a record is inserted, and then that fresh hash inserted into the hashtable.
Are there better solutions to this or is that the only real solution?
edit: I've created an SQL Fiddle of an example of how my system works
tbl_data_2012 will never be written to again.
tbl_recording is constantly being written to but every hour or so, it's the contents it's being emptied to
tbl_recording has already dumped it's contents to
tbl_data_2013 so when the 4th record was written there was nothing in there previously, thus it has created a new
legacyid, however that
legacyid already exists in