Should I have a parent table with only one field?

Question

My database has multiple tables that all rely on dates. I was wondering if it would be smarter for me to take the date attribute and throw it in a parent table, rather than give each table a date field. Here are two case examples (one with parent table, and one without):

Tables with parent table:

Schedule
--
PK ScheduleId : int 
--
Date : date (unique)


Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Time : time(7)
--
FK ScheduleId : int


Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int


GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int

Tables without parent table:

Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Date : date
Time : time(7)



Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
Date : date



GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
Date : date

In the first case illustrated, everything references a unique Date entry. I'm split because I'm not sure if it would be better to search by the indexed foreign keys to get all the events (deliveries, exceptions, and generic events), or if it would be faster/ better practice to botch the Date parent table, and just search all the individual tables by a date field. The data model for the second case would look as follows:

The other bonus I would achieve from doing the first practice is that since I'm using entity framework, the object relationships would already be in place, and I could search for a date, and automatically receive all related deliveries, exceptions, and genericevents.

Thank you for your input, it's appreciated more than you know!

Answer 1

Do not store dates in a separate table unless there is something about a particular date that has additional information that you need to store. In general the date is a simple attribute of some other entity, it isn't an entity in itself unless you have a very peculiar application.

One exception to this might be a star schema data warehouse where there is a time dimension table that might use date as the primary key and have attributes that describe the way that date might roll up - assuming that some roll ups might not be trivial to calculate (like fiscal reporting period or some such).

You can index a date column just as easily as an integer column, so there is nothing to be gained by abstracting out a date so that you can give it an integer alias value.

You can also put a date column in a WHERE clause so searching for records by date is not at all onerous to do.