IMHO, if there is a valid business reason to update, then as an architect, you need to provide a way to facilitate this business requirement but also log every change (i.e. before/after values)

Depends a lot on who you apply the deny to. Please provide specifics. Never say never.

@Paparazzi The deny is applied to any user or role that is not an administrator of that database.

It makes me so happy to see that your timestamp has proper time zone handling.

Just a thought, but databases can't enforce all business rules, so why do you think you should enforce _some_of them on the database layer? Why not keep all rules in one place?

@David I can't keep all the food in the fridge, so why should I keep some of it in the fridge? It's a fairly common sentiment that rules regarding the data should be kept as close to the data as possible. This provides the least opportunity for people to bypass the rules (intentionally or not). If you keep all the business rules in the application, why not keep all the data there too? Do you have a good, real-world example of a business rule that can't be enforced by the database?

@AaronBertrand I have an application where a polygon should be within a US state boundary. But because these polygons largely come from other systems and get uploaded, we don't want to block saving polygons that don't conform while the user is editing. So instead, we validate it in the app logic when the item it's attached to goes from Draft to Submitted for Approval. I could ask the reverse question, too. Why not put all the logic in the DB and not even have a separate web app? It's about choosing the best tool for the job.

@jpmc26 Your example sounds like a workflow constraint, not a data constraint.

@AaronBertrand I prefer even UTC to explicitly have its timezone attached. It doesn't matter much what time zone the data is stored in (unless you have like fractional second accuracy requirements or something odd like that); it matters more that the timezone or offset is readily available for conversion and that there's no ambiguity about the data's meaning. I don't like having to write code that assumes any particular time zone for a piece of data.

@AaronBertrand I don't see the difference. It's a rule about the data itself. We could write a trigger that validates it when the status changes, but we chose not to since the query can be quite expensive and we didn't want something like that triggering accidentally. (Not to mention the fact the status and polygon are on separate tables.)

@AaronBertrand The reasoning is that databases are good at certain things (indexing, querying, horizontal scalability) and bad at certain things (writing programs in them). Sure, I can write a transact SQL script for a Microsoft database which will do more or less anything, but it leads to messy situations where nobody knows whether everything is water tight. Instead a good database abstraction layer handles of this and does not permit connections bypassing it.

@jpmc26 You could also decide to not even bother putting any of the data into the database until they're done. My objection was simply to the notion that since some business rules allegedly can't be in the database, that none of them should be, and I find that rather extreme. As a DBA first, I find it difficult to trust that the people writing the abstraction layer can (a) write the constraints the same way I know I can directly against the data, (b) write them without bugs, and (c) somehow enforce that all users only use the abstraction layer.

@jpmc26 This isn't an argument anyone can win, by the way. I feel I have good reasons to believe that putting my passport etc. in a safe is better than simply trusting the lock on the front door. You might feel like convenience for the developer is more important than the security of the data, and that's fine. I might never convince you to use stored procedures instead of embedding SQL queries inside application code, and that's fine too.

@AaronBertrand "You could also decide to not even bother putting any of the data into the database until they're done." This isn't really feasible when editing occurs over months or later on and the item has to go back into Draft status. I'm not being dogmatic here. I'm just saying that there are cases where it makes sense to have complex constraints somewhere other than the DB, and in such (likely common) cases, David has a point (that he probably could have stated more clearly). Once you have an application layer, adding constraints beyond basic PK, FK, and not null has questionable value.

Despite the fragmented nature of contemporary/traditional technical support, there is not just one database. Hence the classic ANSI/SPARC characterization of two layers, one for the company's "conceptual" database & one for many a user/application "external" database that is a view/function of it. It is irrelevant to your developer's application what databases exist other than its own, be they implementing or paralleling it. However its database should be defined appropriately for it, by a DBA. As much as possible an application's business rules should be imposed by its database.