Date span design

From: Jernej Kase <>
Date: 6 Feb 2003 02:16:45 -0800
Message-ID: <>

Dear all,

I'm designing a database for room rental calculation. I have a question regarding date spans.

Let's say we have the following tables:
  Room [RoomID]
  Contract [ContractID]

And then there is a join table between Room and Contract, which represents "Contract x contains Room z between m/d/yy and m/d/yyy":   ContractRoom

  • fromDate
  • toDate

And then there's the
  ClearingPeriod [ClearingPeriodID]

  • title
  • from date
  • to date

It's obvious that I have to calculate clearance for each contract that falls in ClearingPeriod.

And now my question:
  is it reasonable to design the model in such way, that there is a "Day" table, with datetime as PK, and then join tables ContractRoomDay and ClearingPeriodDay.

The way I see it it would be good in terms of data integrity. But performance could decrease, while there would be many records in ContractRoomDay and ClearingPeriodDay tables.

Any suggestions will be appreciated

  Jernej Received on Thu Feb 06 2003 - 11:16:45 CET

Original text of this message