>>>>- follow up question: do deleted records stay in the SQL tables for recovery if necessary? I would expect that this might be more of a function of database maintenance/purging that is configured hopefully on a table by table setting.
>>>
>>>No. In pretty much every other database, DELETE means delete and there's no way to recover a record. If allowing recovery is an important feature for you,
>>
>>But our (read: "as industry oldtimers") mental map of best practices might be tainted by the technical boundries of last decades ;-)
>>With the technological capabilities of big data available today never deleting ANY data and wrapping our SELECTS around that concept will not run often into problems, neither from size or speed. Both reading in NoSQL and Perf articles I have encountered the opinion that it might be better to never delete (as in destroy) information from the database and see "current" only as a filtered view of a temporal data stream.
>>
>>
>>>you'll need to implement via a field that indicates whether a record is current or not.
>>
>>technical nitpick: in 1:1 inner join relations of multi-category lookup tables the other option is to have a "newest" TS field in the table and add a record for "deleted/not available" for that category/key combination.
>
>Thomas: not getting what you mean by "TS field"?
>
TimeStamp