I have 3 tables (check, active, history) which are all identical in structure (bar constraints on fields).
Data could be initally inserted onto any of the three tables, and is "unique" by way of two fields (docnum + pagenum).
I cannot use a compound primary key (as using ASPxGridView which doesn't like them), using a GUID as a primary key causes issues thanks the asp.net and is not ideal, however the docnum+pagenum is a unique key. So althought the prmary key would be some int value, the validation for uniqueness is dependant on the unique key.
The obvious solution is to use an identity column on each table.
Data will be migrated from one table to another (eg check-> active -> history) and searches are required over both the active and history tables. So there is always the possibility that when retreiving records you will get a duplicate primary key (the identity id field).
I have thought of using a master table that essentially assigns the next number in a int field on any insert to each of the three tables and then increments the number for the next insert but I fear that that will performance overhead.
What options do I have here?