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

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:

>
>"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
>

Yes,they are mainly designed that way for ease of reporting . The underlying highly normalized TP system tables are joined to produce the set of columns most requested for reporting - therefore, we use our indexes for optimum speed when using a where clause ( the tables can have > 10 Million rows of 25 to 30 columns each.) rather than to enforce referential integrity ( so my multi-column index is actually a Unique Index , not a Primary Key in the strict sense.)

• John