Re: composed foreign key question

Mark C. Stock schrieb:

> you're likely to spark a religious debate on this one on the merits of
> natural keys vs surrocate (system-assigned) PKs

That was truly not intended. ;-)

> that aside, the first big question is why can't you create a 2-column FK on
> your option_prices table? why doesn't the following SQL work on your system?
> alter table option_prices add constraint option_prices$underlying#fk
> foreign key (contract,lookupdate)
> references underlying

Well, can't answer that one 'cause it works now... Unfortunatly I didn't save the various statements I tried befor so I can't compare them.

> however, there are a few things suspicious (or just missing) about your
> design:

For clarification:
The contract is really named by the given date (e.g. 'AUG 05', 'DEZ 05') because it is the expiry date of it. Don't ask me I was told this way by the guy for whom I am loading this data. The reason for having the composed primary key is, that this one is a timeline. So the combination of contract and lookupdate is the only way to uniquely identifying the underlying for an option.

> [_] if underlying has a two-column PK, the CONTRACT column should be an FK,
> presumably to a CONTRACT table

We do not have a seperate CONTRACT table, because we do not have any column that is not dependent on both contract and lookupdate.

> [_] CONTRACT is a poorly named column, as it does not contain a contract,
> but appears to reference only a date related to a contract

Under our circumstances it is the name.

> [_] the design not only implies a CONTRACT table, but implies that there can
> only be one CONTRACT per day (or, taken to the extreme, per second)

There is always only one contract(identified by expiry-date) per day. This is a given fact due to the given data.

> [_] a similar implication apples to the UNDERLYING table (a very ambiguously
> names table); by using a date as the 2nd element of the PK, are you implying
> a maximum of one record per contract per day? per hour? per minute second?

answered above (I think...)

> suggestions:
> [_] correctly model the CONTRACT table, likely with a system-assigned (SA)
> PK

I do not see the need for this table, due to fact said above about no repeated information within underlying-table.

> [_] define the UNDERLYING table's purpose and actual relationship to the
> CONTRACT table, and give it a more appropriate name. if it is a true
> dependent table, determine what would be most appropriate for the 2nd
> element of the PK -- likely it will also be a system-assigned ID of some
> sort. if it is not a true dependent table (ie, records can be transferred
> from one contract to another) then likely it should have a single-column SA
> PK

UNDERLYING table gives the information about the options underlying, nothing more nothing less. It's the parent table for sure.

> [_] similar issues with the option_prices table. i'm guessing this is a true
> dependent of whatever UNDERLYING represents, so it will likely have a
> multi-part PK. just determine what is best to use for the non-FK column of
> the PK, again, likely a SA ID of some sort

OPTION_PRICES table is a true dependent on UNDERLYING table, as every option needs to have an underlying. For now I have not figured out a natural primary key for this one and won't likely find one. This is one thing to to an this table, use a SA PK.

Thanks for your help and please keep on helping :-)

    Moritz Received on Mon Sep 19 2005 - 10:48:04 CDT