1

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?

1
  • Excellent point - thank you very much for taking the time to consider and respond. Separate table it shall be!
    – Soap
    Aug 18, 2015 at 9:57

1 Answer 1

1

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.

1
  • @Soap said: Excellent point - thank you very much for taking the time to consider and respond. Separate table it shall be!
    – Marco
    Aug 18, 2015 at 10:52

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.