Too many columns in the composite key after conversion

Discussion in 'Getting Started' started by somsmenon, Jan 22, 2008.

1. somsmenonNew Member

Hi, my project deals with the conversion of a hierarchical database to SqlServer. We would be using SqlServer 2005 after conversion. Currently we are in a dilemma regarding the number of columns within the primary (composite) key. I shall explain the issue with this example.
Relation: Parent Child1 Child2 Child3
Table : Tab1 Tab2 Tab3 Tab4
Main Columns: Col1,Col2 Col3,Col4 Col5,Col6 Col7,Col8
Values : A,B C,D E ,F G,H
No of unique columns in the parent = 2 Col1, Col2
Therefore we need to define a composite key on this table having Col1, Col2
As, the data in the hierarchical database is such that, in order to get a unique row from a child table, we need to refer back till the root parent because there can be data following such a structure:
Parent Ã  Child1 Ã  Child2 Ã  Child3
Tab1 Tab2 Tab3 Tab4
A,B Ã  C,D Ã  E ,F Ã  G,H
A,B Ã  K,L Ã  E,F Ã  G,H
S,T Ã  C,D Ã  E,F Ã  G,H
Child2 is the child of Child2; Child3 is the child of Child2.
As we can see when we download the data from the hierarchical database to SqlServer, in the table Tab4 there can be three values G,H and Tab 3 there can be three values of E,F. But they can have two values referring in the parent tables Tab2 (C,D and K,L), moreover, there can be two values referring C,D in Tab2 in Tab1,i.e, A,B and S,T.
Such a situation has lad us to believe that in order to get a unique row from the table Tab4, we may need the combination of all the keys in the parent tables to be placed in the child table and define that as a primary composite key since only this combination defines the uniqueness. This would mean that, we may have to define a composite key having 8 columns or more for the child tables at the bottom.
We know that this is not a good idea as this will hurt the performance. But we are not able to find out a better solution.
We would like to know how bad the performance is hit in such cases. After conversion, we find that most of the queries in our application are singleton Select statements that select a unique row using all the keys defined in the composite PK. For Cursors, we refer to the keys within the table itself. By this method, the number of joins would be very less, since all the columns are there in the table itself. The only issue is that we would have a PK with large width.
Please let us know whether we could define large composite keys for such cases or is there any other better idea??
2. FrankKalisModerator

Not sure, if I have understood you correctly, but I would consider adding a surrogate PRIMARY KEY of the int data type.
3. somsmenonNew Member

Thanks for the reply. We had considered using a surrogate primay key, but since it is an existing application, addition of a new key would not hold good, as the queries with in the applications would be referring to the existing columns and not the new one.
It would be very helpful if you could explain your ideas in more detail, as I am new to Sql Server.