I was hoping for some advice on best practice in schema design – as far as End Date values are concerned.
In this case it is for a Suppliers table – we are required to store information on the Start Date and – should the relationship finish the End Date.
In an environment with low Supplier turnover – I can see the argument for allowing a nullable end date column in the Supplier Table.
Conversely, in a high turnover environment for pushing SuppierID and EndDate into a separate SupplierEndDate table.
The majority of articles I have read – have concluded that it should be judged on a case by case basis. However, I tend to lean more toward creating SupplierEndDate Table.
Is there a definitive best practice for OLTP schema or at least a favoured approach?
Best Answer
When the supplier 'comes back' then do you want to set the EndDate to null again or do you want to keep track of the periods in which the supplier supplied to you? In the last case you must put the StartDate and EndDate in a separate table. If you put it in a separate table then you always must do a join to see if it is an active supplier unless you put an active Y/N switch in the supplier row.