Normalization and Dates?

[Bryan021]

My DB project is going to have Dates recorded for several different reasons.
Entry Date, Due Date, Sign-off Date, Deposit Payment Date, Template Date, Fabrication Date, Install Date, Final Payment Date, etc, etc.

Do I have all of these fields pointing to a single Date table, so any given "date" is referenced by any of the fields?
OR


Do I have a separate table of each date type, and the actual "date" itself is just data?

How do you deal dates for differing purposes in your DB structure?

[jzwp11]

Generally dates are just data, but it really depends on your application. For example, lets say that you are dealing with projects and related to each project you have a series of dates such as project start date, fabrication date, date to customer, invoice issue date. Each of these things can acutally be considered an event related to the project (start, fabrication, delivery to customer, invoice issued). So if these are typical events (that are common to all projects), you would have these events in a table and then relate them to each project with the date on which it occurs. In other words, a structure like this

tblProject
-pkProjectID primary key, autonumber
-txtProjectName

tblEvents (just holds all possible events)
-pkEventID primary key, autonumber
-txtEventName


Now relate the events to a project

tblProjectEvents
-pkProjEventID primary key, autonumber
-fkProjectID foreign key to tblProject
-fkEventID foreign key to tblEvents
-dteEvent (date of the event specified by fkEventID for the project specified by fkProjectID)

[Bryan021]

Thanks... I didn't think to treat these date related items as "events"... this does simplify things for me.

[jzwp11]

You're welcome

[RuralGuy]

@Bryan
Are you satisfied enough with this thread to use the Thread Tools and mark it Solved?

[Bryan021]

@Bryan
Are you satisfied enough with this thread to use the Thread Tools and mark it Solved?

Certainly... I was giving the thread an extra day to see if it garnered any other opinions before doing so ( a "solved" thread is less likely to gain a viewing).

[RuralGuy]

Thanks for checking back.