Link to home
Start Free TrialLog in
Avatar of Azaniah
AzaniahFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Unique ID, Several SQL Tables

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?

ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Azaniah

ASKER

If doing that would you suggest then having docnum+pagenum as the primary key in the tables and not having any kind of identity field?

Determining which table the record comes from won't be an issue due to constraints on other fields.
That works for me!
Avatar of Azaniah

ASKER

Although that appears to work (and there is a method for creating a composite key in the ASPxGridView) if I did need to create a unique key across those three tables how would that be achieved?
That would be quite tricky....
First you need a table [x] that has a single identity column
Then you need a new column in the 3 tables - and you need to update all existing data with a value
Then you need to add a new int column [uid]
Finally, add a before insert trigger to each table that will perform an insert into [x] with readpast + cleanup and use the scope_identity() generated to populate the [uid] column
Avatar of Azaniah

ASKER

Yes thats very similar to the MasterID table I tested. I suspect that this would be a bit of a performance hit.

I'm not an expert in any of this, but my logic would assume that if I am forcing docnum+pagenum to be unique then it may as well be the primary key. They are both int fields, so the indexing will be fairly good I assume (rathar than using an alpha key - or a random GUID - both of which I suspect have potential performance hits).

Is there much difference in performance hit for a primary index being int+int as oppossed to just a single Int (as an identity field would be)?