Geek asks and answers

Three-way referential integrity - SQL Server 2008

I am creating a database using SQL Server 2008 to store prices for securities that are traded in several markets.

For this market, all securities have the same holiday calendar. However, holiday calendars differ from market to market.

I want the following four tables: Market, GoodBusinessDay, Security and SecurityPriceHistory, and I want to ensure that SecurityPriceHistory does not have rows for business days when the market in which the security was sold was closed.

The fields in the tables are as follows:

Market: MarketID (PK), MarketName

GoodBusinessDay: MarketID (FK), SettlementDate (pair - PK)

Security: SecurityID (PK), MarketID (FK), SecurityName

SecurityPriceHistory: this is a question - my preference is SecurityID, SettlementDate, SecurityPrice

How can I define tables this way and ensure that for each row in SecurityPriceHistory there is a corresponding row in GoodBusinessDay?

If I added a column for MarketID in SecurityPriceHistory. I could see how I can do this with two foreign keys (one indicates "Security" and one indicates "GoodBusinessDay"), but this does not seem to be the correct way to do this.

+3
source share
3 answers

This model should do. The relationship between Market and BusinessDay is determined, that is, the working day does not exist outside the context of the market to which it belongs.

, BusinessDay SecurityPriceHistory , Security SecurityPriceHistory.

, SecurityPriceHistory : security_id, market_id sett_date.

take 1

, SecurityPriceHistory / . , , : , , :

take 2

+1

SecurityPriceHistory FK GoodBusinessDay NO FK Market. , GoodBusinessDay. , .

, GoodBusinessDay /, .

0

, marketID securityPriceHistory, , goodBusinessDay.

, , . , , , .

, marketID securityPriceHistory FK, MarketDay MarketSecurity. , IMO.

0

More articles:


All Articles