I have a large table (~4 million rows, ~100 fields) which I need to split into a few smaller tables by type so it doesn't become unmanageable as it grows.
Each entry has an ID which is an automatically generated primary key. These keys need to be preserved when the data is moved to new tables. Additionally, new unique keys will need to be generated; and these will need to be unique across all of the smaller tables, rather than simply within each table itself.
I was thinking that a way to do this would be to transfer the ID into a new field (or a non-automatically generated primary key field?) when transferring the data into the new tables. Once the data is transferred, I could strip down the original table so it is just a primary key so it can continue automatically generating keys for any additional data - i.e. before inserting data into one of the new tables, get a new primary key from the original table.
However, that seems rather clumsy!
Also it leaves the question, if a user is referring to an ID without knowing what type the data is (i.e. what table it will be found in), how could they be 'directed' to the correct table?
I'm sure there must be a better way to do this?