2 different foreign keys in one table

[HandersonVA]

There is itemlookup table, which stores item number and item
description. Also there is a child table, pricehistory, of the
itemlookup table, which stores different prices and different date
ranges. So it is one-to-many relationship. (Price can be stored more
than one with a different date range)
And there is another table RequestItem that stores the foreign key of
the itemlookup table to show the information of the itemlookup table.
Then how do I know later which date range of the price was stored in
the RequestItem table? Since I only keep the foreign key of the
itemlookup table, it will be impossible to keep track of the row of the
pricehistory table if there are more than one data existed in the
pricehistory table.
Will it be a valid table structure to create a column for the foreign
key of the pricehistory in RequestItem table or any other ways to
handle this issue?

[David Portas]

Re: 2 different foreign keys in one table

HandersonVA wrote:[color=blue]
> There is itemlookup table, which stores item number and item
> description. Also there is a child table, pricehistory, of the
> itemlookup table, which stores different prices and different date
> ranges. So it is one-to-many relationship. (Price can be stored more
> than one with a different date range)
> And there is another table RequestItem that stores the foreign key of
> the itemlookup table to show the information of the itemlookup table.
> Then how do I know later which date range of the price was stored in
> the RequestItem table? Since I only keep the foreign key of the
> itemlookup table, it will be impossible to keep track of the row of the
> pricehistory table if there are more than one data existed in the
> pricehistory table.
> Will it be a valid table structure to create a column for the foreign
> key of the pricehistory in RequestItem table or any other ways to
> handle this issue?[/color]

It sounds like RequestItem may need to include the date or some other
key that references PriceHistory. It isn't obvious whether RequestItem
would also require a foreign key on the Item table. Please post DDL
instead of long narratives, then we won't have to guess. There is
nothing logically wrong with having two foreign keys on one table
however.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--