The model below is an example of an Event and Event Line concept, a familiar structure in many of our organizations. Each of these entities contains data elements that are not shown on this model as well as other relationships that are not shown such as to Customer and Supplier.
Once an Order is considered “closed” and leaves the system, an Order Number value can be used again on a future order. You’ve heard the slogan “Recycle, Reduce, Reuse” – here it is being applied to natural key values. Therefore Order Number is not a unique natural key. We can add the Order Create Date to Order’s primary key (Option 1 below), or create a surrogate key called Order Id and make Order Number and Order Create Date an alternate key (Option 2 below).
Would you choose Option 1, Option 2, or is there a better solution and why?