WOW!
Where to start....
in a properly structured RDBMS where business entities are modelled correctly, constraints are never going to slow down your application. There are plenty of other reasons (missing indexes, partitioning, non SARGable queries) but check constraints and foreign keys are not going to be it.
SOMEONE needs to be responsible for the integrity of the business data. I agree that overly strict RDBMS constraints can APPEAR to slow down development but in reality they do not; they are like the canary in the mine and act as an early warning system that the business entities are not correctly modeled. If you don't do it at the database level you have to do it somewhere else. Most business solutions these days will have multiple entry points: Desktop, mobile and external applications. It is now possible to move the integrity checks to the API layer if it is common to all applications but DO NOT leave it up to the front end devs to encapsulate the business rules.
Strict RDBMS integrity rules FORCE changes in business requirements to be properly considered and investigated. You can't create a Subscription without the User, but without referential integrity built into the database model who is responsible for checking that you can delete the customer and not leave the Subscription orphaned.
I am going to assume that your example of the unique constraint for a User being the email address was contrived, but I fear it may not have been. There is a difference between the entity key and a unique constraint although there are often interchangable. the entity key should be unique AND idempotent (i.e. cannot change) AND ubiquitous (i.e. every entity must have one). For many reasons this will often become a NONCE key; either a UUID or an IDENTITY INT; it has no business significance outside the database. This fundamental failure of understanding is why you MUST engage a database architect and not leave entity definitions to the developers (sorry devs, but strings aren't strings. They are CHARS, VARCHARS, NVARCHARS, TEXT and have a length and a collation - otherwise you waste space and performance in my database). As a Database guy, I would not expect to know all the subtelties of your programming domain so why should you expect to know all the GOTACHAs in mine.
At the end of the day it will be the database people that are tasked with sorting out any crap data that creeps in through the front end and API layers.
NOONE puts data directly into my tables, all APIs call stored procedures for C_UD and will use a stored procedure or view for the R(ead). This is the only way to really validate that the data being entered is fully kosher. You say that we don't do turing complete code in SQL - Sorry. I do (MSSQL) and in the Oracle world PL/SQL is specifically designed to do this.
Performance is indeed a touchy subject, and I do agree that database constraints should (at least theoretically) have similar or even better performance than application layer constraints - because the database is in fact optimized specifically for that.
Modelling business entities is not the job of the data layer. I know, people will disagree on this, but I firmly believe that the data layer should not be opinionated about business logic. It is storage, and it exists to model data structures, not business structures.
As I said in my other comment, the constraints the data layer should be responsible for are relationships (in a relational DBMS), and internal integrity (that is, valid types, limits, and so on).
While I agree that a lot of people eschew an API layer, that's bad practice as well. There should absolutely be an API core that models the actual business logic.
Jon is a self-taught programmer, started in video games but now does web development. He follows principles, argues for scientific software development, and does not like writing in the 3rd person.
It is now possible to move the integrity checks to the API layer if it is common to all applications but DO NOT leave it up to the front end devs to encapsulate the business rules.
Fully agree here. I didn't even realize I was implying anything but keeping the API layer responsible for business requirements.
As for the rest, I really do like your clear angle and rebuttal, even if I disagree. I oppose the sentence "my database", because I want to break down silos and optimize for shared ownership of the product. I suppose you could just as well argue I'm an API-centric chauvinist who wants the world to revolve around the API and you wouldn't be entirely wrong, I consider the DB "just storage" and ultimately not worth super-specializing into (unless you're the vendor building one!) because it doesn't provide customer value directly.
So I think we're fundamentally pitted against each other because I want developers to not care about CHARS, VARCHARS, etc. Those are implementation details to be changed at a whim to cater to whatever business requirements provide value.
Thankfully it's fine we're "pitted against each other" because I love hearing your angle, it's so different from mine and we can only learn from our differences. It might be best we don't work on the same project haha 😅, but thank you for taking the time to elucidate your angle.
Its not a war, at the end of the day we are all on the same team trying to provide value to the business. My point is that constraints in the RDBMs database are there for a reason; it prevents rogue application code from destroying fundamental business data. If you are not going to use them you might as well use a document database or flat text files. Just like your C# / java / python classes are trying to model the real world, so is the relational database. As it is the last line of defense, it HAS to be correct. If front end and middleware developers are creating workarounds then this is an indication that the database model is out of date.
I once did a lignting talk at our local geek-meet about how code-first ORMs (specifically Entity Framework, but the same applies to Eloquent) can help you to rapidly prototype the application and supporting database, but the data types it uses are not optimised (unless you use Fluent, in which case you might as well use DDL in Management Studio) Using a repository layer and Active Record pattern can help but it is still no substitue for a properly configured database.
Some of the crap I have seen:
Dates stored in datetime fields.
Date and time stored in SEPARATE datetime fields.
dates stored as strings
Y/N held in VARCHAR(1000)
Phone number 1, Phone number 2, Phone number 3 ....
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
WOW!
Where to start....
in a properly structured RDBMS where business entities are modelled correctly, constraints are never going to slow down your application. There are plenty of other reasons (missing indexes, partitioning, non SARGable queries) but check constraints and foreign keys are not going to be it.
SOMEONE needs to be responsible for the integrity of the business data. I agree that overly strict RDBMS constraints can APPEAR to slow down development but in reality they do not; they are like the canary in the mine and act as an early warning system that the business entities are not correctly modeled. If you don't do it at the database level you have to do it somewhere else. Most business solutions these days will have multiple entry points: Desktop, mobile and external applications. It is now possible to move the integrity checks to the API layer if it is common to all applications but DO NOT leave it up to the front end devs to encapsulate the business rules.
Strict RDBMS integrity rules FORCE changes in business requirements to be properly considered and investigated. You can't create a Subscription without the User, but without referential integrity built into the database model who is responsible for checking that you can delete the customer and not leave the Subscription orphaned.
I am going to assume that your example of the unique constraint for a User being the email address was contrived, but I fear it may not have been. There is a difference between the entity key and a unique constraint although there are often interchangable. the entity key should be unique AND idempotent (i.e. cannot change) AND ubiquitous (i.e. every entity must have one). For many reasons this will often become a NONCE key; either a UUID or an IDENTITY INT; it has no business significance outside the database. This fundamental failure of understanding is why you MUST engage a database architect and not leave entity definitions to the developers (sorry devs, but strings aren't strings. They are CHARS, VARCHARS, NVARCHARS, TEXT and have a length and a collation - otherwise you waste space and performance in my database). As a Database guy, I would not expect to know all the subtelties of your programming domain so why should you expect to know all the GOTACHAs in mine.
At the end of the day it will be the database people that are tasked with sorting out any crap data that creeps in through the front end and API layers.
NOONE puts data directly into my tables, all APIs call stored procedures for C_UD and will use a stored procedure or view for the R(ead). This is the only way to really validate that the data being entered is fully kosher. You say that we don't do turing complete code in SQL - Sorry. I do (MSSQL) and in the Oracle world PL/SQL is specifically designed to do this.
A few points:
Fully agree here. I didn't even realize I was implying anything but keeping the API layer responsible for business requirements.
As for the rest, I really do like your clear angle and rebuttal, even if I disagree. I oppose the sentence "my database", because I want to break down silos and optimize for shared ownership of the product. I suppose you could just as well argue I'm an API-centric chauvinist who wants the world to revolve around the API and you wouldn't be entirely wrong, I consider the DB "just storage" and ultimately not worth super-specializing into (unless you're the vendor building one!) because it doesn't provide customer value directly.
So I think we're fundamentally pitted against each other because I want developers to not care about CHARS, VARCHARS, etc. Those are implementation details to be changed at a whim to cater to whatever business requirements provide value.
Thankfully it's fine we're "pitted against each other" because I love hearing your angle, it's so different from mine and we can only learn from our differences. It might be best we don't work on the same project haha 😅, but thank you for taking the time to elucidate your angle.
Its not a war, at the end of the day we are all on the same team trying to provide value to the business. My point is that constraints in the RDBMs database are there for a reason; it prevents rogue application code from destroying fundamental business data. If you are not going to use them you might as well use a document database or flat text files. Just like your C# / java / python classes are trying to model the real world, so is the relational database. As it is the last line of defense, it HAS to be correct. If front end and middleware developers are creating workarounds then this is an indication that the database model is out of date.
I once did a lignting talk at our local geek-meet about how code-first ORMs (specifically Entity Framework, but the same applies to Eloquent) can help you to rapidly prototype the application and supporting database, but the data types it uses are not optimised (unless you use Fluent, in which case you might as well use DDL in Management Studio) Using a repository layer and Active Record pattern can help but it is still no substitue for a properly configured database.
Some of the crap I have seen:
Dates stored in datetime fields.
Date and time stored in SEPARATE datetime fields.
dates stored as strings
Y/N held in VARCHAR(1000)
Phone number 1, Phone number 2, Phone number 3 ....