Search code examples
sql-serverforeign-key-relationshipreferential-integrity

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.

Solution

  • 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.

    take 1

    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:

    take 2