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_2012, 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:

  1. After data is removed from tbl_recording the 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
  2. Between years, the same hash could be generated for 2013 and 2014 EDIT: 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


pretend that: 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_data_2013. 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 tbl_data_2013

  • Why do you hash legacyid instead of just keeping it? – Matthew Nov 12 '13 at 18:27
  • what do you mean? legacyid is just a varchar which will be a hash. – Jarede Nov 13 '13 at 9:35
  • deleted mine answer... first place some create table statements and some example data on SQLfiddle so we know want you are talking about.. and you will never avoid unique key collisions across tables partitioning is the only logical approch for this.. – Raymond Nijland Nov 13 '13 at 10:19
  • see recent edit @RaymondNijland – Jarede Nov 13 '13 at 10:44
  • @Jarede what is it a hash of? – Matthew Nov 13 '13 at 16:46

You should separate the table on year to get faster insert's, updates or deletes because we expect lots lots lots lots lots lots off records in this table..

**But... your problem is precisely the reason why separating tables on meta data (year in your question) is an SQL anti pattern, you could not have giving us an better use case*

If you want an better solution you should read about mysql partitioning see http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

  • with the removal of my second concern by appending the year, partitioning would still not help with the first concern, surely? – Jarede Nov 13 '13 at 10:10

I'm not aware of the ins and outs of MySQL as I'm a SQL Server guy myself, but what's the reasoning for splitting out your data into yearly tables?

I'm thinking doing something with table partitioning where you partition on the year. This would give you the ability to keep all the data together in one logical table and enforce uniqueness as well and give you the ability to spread out the physical locations of each partition.

This would also simplify things like reporting.

  • undelete mine answer and because ive also had mentioned table partitioning.. – Raymond Nijland Nov 22 '13 at 23:01
  • Somehow I missed that. My bad. – njkroes Nov 22 '13 at 23:12
  • Well it's not your bad.. i did "delete (marks it invisible for all users expect for me)" that answer and "undelete" what is possible here.. – Raymond Nijland Nov 22 '13 at 23:21
  • 1
    I'm more of an Oracle person myself. But I think this conversation is going around in circles until we all clarify why partitioning is useful and why multiple tables with the same structure and semantics is an antipattern. You and Raymond are saying the right things, but it isn't getting through. – Walter Mitty Nov 26 '14 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.