4 ms·

I would never use constraints ever again. Period. Billions of rows of data to POCs. It's interesting to note that AMZ data largely runs without constraints (th

by jack9 9y ago

I would never use constraints ever again. Period. Billions of rows of data to POCs. It's interesting to note that AMZ data largely runs without constraints (the largest systems). Working in Southern California, where tenures run a few years before you end up somewhere else, everyone comes to a consensus in a decade. It's obvious to me that embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder. Then you run into the inevitable of data changes and fighting the existing constraints. It's a waste of effort, but an obvious path-to-job-security for DBs. > And of course you can get quite clever and fun here....create a function that checks if a function is a fibonacci number The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work.

ams6110 9y ago
A lot of things that are really, really good ideas for most applications might not apply at Amazon scale. Most of us don't work at Amazon scale. I agree on the fib constraint, though. Fun, but nothing you'd ever really do. > embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder Not in many cases. In the past 15-20 years, think about how many application stacks have come and gone. We've had Postgres that whole time, and plsql. Core business logic in the DB never needs to be rewritten, no matter how many different front-end rewrites you go through.
- falcolas 9y ago
  With your last point, you're conflating language longevity with code longevity. Code in the DB changes as often as any other code: as often as the business requires it. The last company I worked for who used stored procedures had a change for those stored procedures with every frontend release. And logic in the DB is inherently capable of only scaling as large a your DB cluster (i.e. not very), and can cause your DB cluster to scale prematurely (an event which incurs a lot of potential technical debt and additional monitoring).
deleted 9y ago
[deleted]
tatersolid 9y ago
Do you rely on file system permissions as a sanity check to your application code being correct? (If you don’t you probably are in the wrong job) A filesystem is just a hierarchical key-value database. Assuming you do believe in file permissions, why wouldn’t you use the additional sanity of validation available in a SQL database?
jimktrains2 9y ago
> I would never use constraints ever again So you'll never again use a uniqueness or foreign key constraint? Also, I don't know what pocmeans, bit it provides no insight into the problems you had and why it was ok to accept otherwise bad data. > The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work. Yes, a cute example showing you that you can constrain by anything is indicative of how terrible a place this is! /s
segmondy 9y ago
Sorry but to put it bluntly, this is a very ridiculous thing for you to stay. I tell everyone, it's easy to remove constraints, but it's very difficult to add it once your data has been polluted. Think of constraints like a canary in the mine for your data. If things are wrong or perhaps have changed, it alerts you. If you can expand it, you do so, if you have no choice, you can drop it. But always begin with constraints!
mdpopescu 9y ago
I don't understand why. I see constraints doing the same job as objects to in programming: they keep the data and the rules for manipulating that data together, ensuring that the invariants are always respected. If "you can't have a price <= 0" is a business rule then it should be enforced in all layers: in the UI because you want to warn the user they're trying to do something incorrect without sending obviously invalid data to the server, in the back-end because you don't want to write incorrect data to the database, and in the database because you don't know that someone else will not write "just a quick app" that will bypass your back-end and mess up the data.
- vbezhenar 9y ago
  Problem with constraints is that they reflect current business logic, but database stores past data. This is inherent mismatch and it'll cause conflicts. You can't add constraint which conflict with old data, yet you need to check that constraint for current data, therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. If you have duplicate constraints, you must not forget to keep them in sync. I'm not saying that constraints are bad, database without constraints would be a very strange beast, even UTF-8 text is a constraint upon arbitrary byte strings, but it's a dangerous construct and must not be misused, otherwise it'll cause more harm than good.
  - always_good 9y ago
    > You can't add constraint which conflict with old data Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something. I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.
    - Demiurge 9y ago

by jack9 9y ago

ams6110 9y ago

A lot of things that are really, really good ideas for most applications might not apply at Amazon scale. Most of us don't work at Amazon scale. I agree on the fib constraint, though. Fun, but nothing you'd ever really do. > embedding business logic in SQL along with the server app and whatever presents data to third parties, is just making everything harder Not in many cases. In the past 15-20 years, think about how many application stacks have come and gone. We've had Postgres that whole time, and plsql. Core business logic in the DB never needs to be rewritten, no matter how many different front-end rewrites you go through.

falcolas 9y ago
With your last point, you're conflating language longevity with code longevity. Code in the DB changes as often as any other code: as often as the business requires it. The last company I worked for who used stored procedures had a change for those stored procedures with every frontend release. And logic in the DB is inherently capable of only scaling as large a your DB cluster (i.e. not very), and can cause your DB cluster to scale prematurely (an event which incurs a lot of potential technical debt and additional monitoring).

deleted 9y ago

[deleted]

tatersolid 9y ago

Do you rely on file system permissions as a sanity check to your application code being correct? (If you don’t you probably are in the wrong job) A filesystem is just a hierarchical key-value database. Assuming you do believe in file permissions, why wouldn’t you use the additional sanity of validation available in a SQL database?

jimktrains2 9y ago

> I would never use constraints ever again So you'll never again use a uniqueness or foreign key constraint? Also, I don't know what pocmeans, bit it provides no insight into the problems you had and why it was ok to accept otherwise bad data. > The fact this is presented as a reasonable (trivial!) thing to embed in the DB screams, this is a terrible place to work. Yes, a cute example showing you that you can constrain by anything is indicative of how terrible a place this is! /s

segmondy 9y ago

Sorry but to put it bluntly, this is a very ridiculous thing for you to stay. I tell everyone, it's easy to remove constraints, but it's very difficult to add it once your data has been polluted. Think of constraints like a canary in the mine for your data. If things are wrong or perhaps have changed, it alerts you. If you can expand it, you do so, if you have no choice, you can drop it. But always begin with constraints!

mdpopescu 9y ago

I don't understand why. I see constraints doing the same job as objects to in programming: they keep the data and the rules for manipulating that data together, ensuring that the invariants are always respected. If "you can't have a price <= 0" is a business rule then it should be enforced in all layers: in the UI because you want to warn the user they're trying to do something incorrect without sending obviously invalid data to the server, in the back-end because you don't want to write incorrect data to the database, and in the database because you don't know that someone else will not write "just a quick app" that will bypass your back-end and mess up the data.

vbezhenar 9y ago
Problem with constraints is that they reflect current business logic, but database stores past data. This is inherent mismatch and it'll cause conflicts. You can't add constraint which conflict with old data, yet you need to check that constraint for current data, therefore you must implement those constraints somewhere else and if you have those constraints there anyway, why bother with additional ones. If you have duplicate constraints, you must not forget to keep them in sync. I'm not saying that constraints are bad, database without constraints would be a very strange beast, even UTF-8 text is a constraint upon arbitrary byte strings, but it's a dangerous construct and must not be misused, otherwise it'll cause more harm than good.
- always_good 9y ago
  > You can't add constraint which conflict with old data Of course you can. For example, constraints inside triggers. A simple insert trigger will only apply on records going forward. Also, the reason why you bother with "additional" constraints by implementing them on the database is because the database is typically the thing that can enforce them. Unless you go out of your way to roll your own single-write via queue or something. I'm not sure I agree with your take-away since you can just drop constraints. Easy come, easy go. Meanwhile bad data is easy come and potentially a nightmare to fix.
  - Demiurge 9y ago