Foreign key on a child table dependant on parent field

Question

Using SQL2005/2008. Let's say I have a Hotel table (HotelID, HotelName) and a Room table (RoomID, HotelID, RoomName) Hotel -> Room 1:M by HotelID RoomID is PK inside Room Now I have a Contract table (ContractID, HotelID, ContractDescription) and ContractRate (ContractRateID, ContractID, RoomID, Rate) All primaries and foreign keys defined but, ¿What's the best way to relate RoomID in ContractRate with Room, taking in account that RoomID can only by from the values asociated with HotelID in Room? Currently I have a FK on ContractRate.RoomID -> Room.RoomID but this needs the additional filter of Room.HotelID being ContractRate -> Contract.HotelID, wich I can enforce at the application but would like to know if can be defined/enforced at the database (or if I have to redefine the DB) Best regads, Juanro

Answer 1

I think you have to put RoomID in both contract and ContractRate table for normilization. but in current scenario nobody doing the Normilization so put both hotelID and RoomID in the both table as per my suggestion.