0

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!

  • I see only pain in the 1st road. And why are you naming the table (and columns) Date and DateId when it's a datetime? – ypercubeᵀᴹ Mar 17 '15 at 17:45
  • My apologies, I've changed the types. I've also changed the names to something more suitable. Could you elaborate why you see trouble going down the 1st road? – Michael King Mar 17 '15 at 17:54
  • 1
    Why stop at dates? Why not store numbers and strings the same way? Silly idea. – Colin 't Hart Mar 17 '15 at 18:37
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.