Multiple Date Values for a Single Fact Row

In modeling a hotel scenario, there can be two date values for a single fact row: when the booking was made and when the stay date occurred.

When modeling this scenario, is it advisable to have
1) a single date table tied to both of these fields (i.e. the fact table joins to the date table twice)
2) or two independent date dimension tables (one for booking date and one for stay date).

3) Or, should there be one table containing both date dimensions, and in the fact table have a date code that relates back to the dimensional table corresponding to the intersection of the booking date and stay night date? This would result in a HUGE date table as there would have to be a host of horizon dates (booking dates) leading up to the stay night date.

Why on earth would you consider two separate date tables? Given any data warehouse would have many, many date references, are you saying there should be dozens of date dimension tables? And what about customer? If someone purchases a gift for another, does that mean two customer tables?

There is no performance advantage to having separate tables. In fact, if anything, it can be a significant impediment as it requires the database to physically read more data than it otherwise has to. Plus, you have to store more data, back up more data, maintain more database objects, etc... You also ignore the fact that a database engine has access to memory which is used, for the most part, to cache data so it doesn't need to read disk again. In other words if it needs data for the 'other' date that happens to be in the same block of data it just read, it doesn't need to read disk. If there are separate tables, this would not happen.

The interval component of modeling data in hospitality, where there can be several post dates for a single stay night date, really makes the data balloon as future dates come into the horizon. This is something I am planning for carefully. I appreciate your input on this subject though. Perhaps it is not as serious an issue as I first believed.

You need to understand the concept of 'Role Playing Dimension' to be on the same page as Ngalemmo. It comes down to the core principle of dimensional modeling, Dimension Conformance. It is more obvious when a dimension is shared by multiple fact tables than playing multiple roles in a single fact table. To connect to the fact, a role playing dimension behaves like multiple tables in form of multijoin statements, but it should always be a single physical table.

Option 1. This goes for any dimension. Always role name the foreign key columns when more than one relationship exists.

I agree with Hang. Dimensional role-playing is likely the answer to my question.

Will use views, but stick with one single physical table. Thank you.