This may be a stupid question.
Some efficiency advice for SQL Server 2008 R2 although these servers will be upgraded to 2014 over the next few months. I am creating 3 tables.
My first thought concerns the statement
... the [t2|t3] numeric ID will be sent by the frontend application ...
If you are generating unique identifiers for use in a database I would suggest you let the database generate the numbers. Unless you can be absolutely sure the application will not generate duplicate IDs.
In response to your comment, here's the difference when using surrogate keys (if I have understood your table structure correctly)
+----+ +----+ +----+ | | | | | | | t1 +---+ t2 +---+ t3 | | | | | | | +----+ +----+ +----+ keys: t1.id t1.id t1.id t2.id t2.id t3.id
A change to surrogate keys it would not change
t1 (no need);
t2 would also remain the same, but you would need to change the
t2.id from being a line number to a unique number primary key [pk]. Trouble is you will have to store the 'line number' as another field in the table.
The main difference would be
t3 only needing the
t2.pk and its own row identifier, rather than all three table identifiers.
+----+ +----+ +----+ | | | | | | | t1 +---+ t2 +---+ t3 | | | | | | | +----+ +----+ +----+ keys: t1.id t2.pk t2.pk t1.id t3.pk
Would this be better? I'm not sure. It would also depend in the type, and frequency, of the queries you are running against the tables (see my comment on your question).
If you mainly query by
id then the first layout will be the best - lookup rows in
t3 without having to join to
t1. But if you need information from those other tables to do your searches, a surrogate key structure may make your joins less verbose.
Ultimately, this is all premature optimisation (in terms of query performance) because you won't know what performs best until you implement it.