Re: Create a foreign key from a field that is part of a mulitple primary key

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:4r3hj0hk53eru2h228cb08qchb9gq2ehsd_at_4ax.com...

Our data is not suitable for single field uniqueness ( and we do not produce the data - it is from state-wide data warehouse
| view(s) ) since the only difference between records, in many cases , is
in a combination of fields ( 5 fields in the index
| to make it unique - only one may change between records ) ..The underlying
data structures we need to use are already
| denormalized for reporting purposes and are views based on several
underlying tables from a TP system that we cannot access
| directly..So my constraints ( on my design, not on the data) are in the
source of the data and it mandates that we use such a
| key structure.
|
| This is not to imply that this is a good design, just that it is a
necessary one given the circumstances...
|
| Thanks for taking the time to post a serious reply...
|
| John
|

thanks for the additional comments

to be fair, i was focussing on the conceptual data model vs the table implementation; the compromises and constraints (design, not database) applied to the implementation model typically affect the structure of the tables in the way you described

so, with your denormalized data from an external source, the multi-column PK tables contain references to tables that are not in the scope of your system, but your tables likely contain attributes of those out-of-scope tables (or they're not out totally -of-scope, but as you indicate, they're just denormalized for performance). it's always a real good idea to know (have documented) what's been demormalized and what out-of-scope tables/entities are referenced by the non-unique portion of the mult-column PK (ie, when you strip off the 5th column, what do the other 4 refer to? 4 other tables? 2? 3? 1?). that way as new functionality is required, developers and dbas can determine if the implementation model needs to be expanded to include the other tables. sounds like that's exactly what you've got in your scenario

++ mcs Received on Fri Sep 03 2004 - 11:36:49 CDT