An Orders
table I'm dealing with at present in design phase has about 10 or more columns.
Each OrderID
in the Orders
table will have certain significant events or milestones. The number of Orders
could go into the millions. At the moment, I'm aware of about 20 important events to track for each Order
, but that number may increase or decrease.
Of these 20 or so events, only a handful will always take place, while the rest are just potential events. Also, many - but not all - of these events have at least two points which need storage:
- the Date the event is supposed to happen (if it is no longer just a "potential" event)
- the Date the event did happen
The events data will be updated in the database from external sources in the form of .xls
or .csv
files. For all events, it is only possible for the given event to happen once.
What would be the best design for storing this milestone event data?
Would you try to use a somewhat normalized design, where there is an Events
table designed similar to this...
CREATE TABLE dbo.Events (
EventID INT
, OrderID INT
, EventExpectedDate DATEIME2(7)
, EventActualDate DATETIME2(7)
, EventTypeID INT
, EventSkip BIT
...
)
Or would you try a less normalized approach and flatten all the possible events out into one very wide events table that mimics what the .xls/.csv
update files will look like?
CREATE TABLE dbo.Events (
EventID INT
, OrderID INT
, Event_1_ExpDate DATEIME2(7) /* No, the actual names would not include 1, 2, 3 */
, Event_1_ActDate DATETIME2(7) /* The actual names would name type of Event */
, Event_2_ExpDate DATETIME2(7)
, Event_2_ActDate DATETIME2(7)
...
, Event_20_ExpDate DATETIME2(7)
, Event_20_ActDate DATETIME2(7)
)
Or is there another pattern you would use?