Three-way Referential Integrity - SQL Server 2008

I am building a database using SQL Server 2008 to store prices of securities that are traded on multiple markets.

For a given market, all the securities have the same holiday calendar. However, the holiday calendars are different from market to market.

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

The fields in the tables are as follows:

Market: MarketID (PK), MarketName

GoodBusinessDay: MarketID (FK), SettlementDate (the pair is the PK)

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

SecurityPriceHistory: This is the question - my preference is SecurityID, SettlementDate, SecurityPrice

How can I define the tables this way and guarantee that for every 开发者_如何转开发row in SecurityPriceHistory, there is a corresponding row in GoodBusinessDay?

If I added a column for MarketID to SecurityPriceHistory. I could see how I could do this with two foreign keys (one pointing back to Security and one pointing to GoodBusinessDay), but that doesn't seem like the right way to do it.

---

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

Similarly, the relationship between BusinessDay and SecurityPriceHistory is identifying, as it the relationship between Security and SecurityPriceHistory.

This means that the primary key of SecurityPriceHistory is composite: security_id,market_id and settlement_date.

This will enforce the constraint that each security may be have no more than one row in SecurityPriceHistory for a given market/business day. It does, however, allow for the same security to trade in multiple markets, despite the security's relationship to a particular market: to restrict that, the relationship between Market and Security needs to be identifying, thus:

---

SecurityPriceHistory could have a FK to GoodBusinessDay and NO FK to Market. You could figure out the market by joining to the GoodBusinessDay table. I don't really like this option, but it's a possibility.

You could also use a trigger to check and make sure that there's a proper GoodBusinessDay record on insert/update, otherwise reject the transaction.

---

I think you're going to need the marketID field in your securityPriceHistory table, assuming the same securityID could be sold in different markets on the same goodBusinessDay.

Setting it up the way you're thinking is fine. You have a parent, two related children, then a grandchild that has relationships with both children.

---

Even if a security can only be sold on one market, I'd still include the marketID in the securityPriceHistory table, with two compound FKs, MarketDay and MarketSecurity. Clearer that way, IMO.