5 ms·

Every one of his complaints is a practice I've constantly yelled at everyone around me not to use -- for my whole career. Application logic belongs in the appl

by jbooth 16y ago

Every one of his complaints is a practice I've constantly yelled at everyone around me not to use -- for my whole career. Application logic belongs in the application. Don't rely on a database to do it for you, or you're going to wind up with a giant ball of mud driven by side-effects.

kingkilr 16y ago
Don't rely on the database to not put null in a column marked not nulls? That's like saying don't rely on C to not put a struct in a char*. It's got nothing to do with application logic.
- ars 16y ago
  He's wrong about that BTW. It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000). Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?
  - dagheti 16y ago
    Inserting a default value is even worse than inserting NULL. Now you can't tell what data was wrongly inserted if you tried to clean up the mess later.
    - ars 16y ago
      What? Have you ever programmed SQL? Because what you wrote doesn't make sense. No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field. Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that. The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.
      - dagheti 16y ago
        Oh I agree they are both very bad. Putting NULL in a non-NULL field is totally wrong. However I really do think that inserting an unexpected default value is worse than inserting NULL into a NON-NULL field. The NULLs will cause problems, but they are problems you can see and resolve. The default values are silent errors that will corrupt your data and be very difficult to recover from in the future. You can only guess which data was wrongly inserted.
        ars 16y ago
        I still don't see how it's possible to insert wrong data, or corrupt it?? There IS no data. How do you corrupt something that doesn't exist? And NULL doesn't help either. NULL is valid data, NULL is not a replacement for programming errors (which is what this is). This argument is pointless. People love to bash on MySQL, they look for the silliest things. The more popular something is the more people bash on it. I understand that, but at least bash on real problems? Like the transaction DDL - that's a real problem. This? This is nonsense. (It's actually a very useful - and optional - feature BTW.)
        Lewisham 16y ago
        I'm not sure you understand the use of NULL. NULL is not "default value" or "I don't care", NULL signifies "this might have a value, I just don't know what it is". There is a very significant difference between a payroll record which states your pay is "0" vs. NULL. If the database is putting in default values, you have no way of knowing whether the employee really did have a salary, but it was incorrectly inserted as NULL, or whether the employee is unpaid.
        ars 16y ago
        I understand NULL very well, and that's not the only use for a NULL. NULL also means "value does not exist", not just "value is unknown". For example if a student is not in a class, put NULL in the class id. NULL is perfectly valid data, and is not a replacement for a programming bug. And with mysql if your salary field is defined as accepting NULL then you will get a NULL in there. And to use your example if the field accepts NULL, you would also have no way of knowing if the salary was not negotiated vs a programming bug. If you want to argue the insert should fail, then fine, no problem. (And MySQL can do that.) But arguing that putting in NULL is better (in a field that does not accept NULL), is simply wrong. I'll say it again: NULL is not a replacement for a programming bug - NULL is valid data, and should not be used to find programming errors.
    - kls 16y ago
      Correct, NULL is NULL, if the system is designed that it has to have a default value it should at least scream at you "I can't do NULL, you are going to get a 0 here". Or better yet just error at design time, not run time, and say sorry put a value here I don't do NULL. Run-time errors are the worst kind of errors and it is little unexpected behaviors like that, that can create huge problem in a run time environment.
  - deleted 16y ago
    [deleted]
Lewisham 16y ago
I don't see anywhere where he is calling for application logic. He's making sure that the data has integrity, and the database absolutely should be in charge of that. If your database can't enforce integrity, then you're in a very, very bad place. Putting in sane integrity checks to a database should be a priority. You can fix broken applications, but you can't fix broken data (especially if you collected it from users, sensors).
dasil003 16y ago
What if you have multiple applications talking to the same database?
- derefr 16y ago
  I think, in 2010, a "cloud-based web platform with a RESTful API" is 1990's "database." We don't let multiple applications talk to a single database; we stick a web server in front of the database and make them talk to that. If all the web API is doing is allowing CRUD on your data (with validation and some minimal triggered interactions) then, in effect, you're just creating your own, completely custom DBMS by doing this.
  - mmt 16y ago
    in effect, you're just creating your own, completely custom DBMS by doing this. I've argued for years that this is what the "sharding" hype (and to some degree the current NoSQL hype) was mostly about. I'm not suggesting a custom DBMS can't possibly be the right answer, but it seems silly to layer it on top of something as heavyweight as a relational database.
    - randallsquared 16y ago
      If you don't use relational databases, you're throwing away decades of work into a problem that's very similar to the one you're trying to solve. If you have a brilliant new scheme that forces you to do that, fine, but throwing away finely tuned systems because complex and relatively opaque might not be the best first step.
      - mmt 16y ago
        Exactly. I would advocate not throwing any of it away, absent rare circumstances. I implied, though foolishly didn't state outright, that I believe a custom DBMS is only very rarely the best answer.
  - dasil003 16y ago
    Yes, and this has a lot of advantages. One advantage is openness of APIs for mashups etc. But the big advantage driving the NoSQL/custom DB is of course scalability and performance. Incidentally performance was also a key consideration 40 years ago when everyone rolled their own custom data stores. The modern SQL database evolved as a response to all the problems that created, which by now are long forgotten. My point is not to throw the baby out with the bathwater. If you need massive scale then of course you need fresh approaches, but you are sacrificing a lot by abandoning a centralized DB. If your data is moderately sized, then you will be trading a lot of data integrity, queryability and flexibility by giving up an SQL databases. Certainly consumer web services of the type that are en vogue in silicon valley need new approaches. However the majority of applications out there are probably still best served primarily by a relational DB, especially when you consider the value of a byte of corporate data vs the value of a byte of facebook data.
masklinn 16y ago
There was nothing about application logic in the post. Quite the opposite in fact (transactional DDL and deferred constraints are things which have no reason to be handled in the applicative layer). It was only about the internal consistency of the data.
tomjen3 16y ago
I made the same argument to my database teacher. He pointed out that isn't going to do you much good when you have more than one application talking to your database, since you would then have to write the code to validate it in each application.
- ryanpetrich 16y ago
  Better yet, require that all applications call through some common API (such as a library or web service) to access the data.
  - ora600 16y ago
    You can require this, but its tricky to enforce. Especially on DBAs.
  - electrum 16y ago
    Why not put that API inside the database (i.e. stored procedures and views)?
ora600 16y ago
In any database comes a time where a developer or administrator will have to make manual changes to the data through an ad-hoc script. It can be straight sql or perl/php/ruby thing. The easier you'll make it on him not to cause horrible corruptions in the data by forgetting to update that additional table that depends on whatever he is updating - the better life will be. Triggers, foreign keys and constraints are all excellent ways to do just that.

by jbooth 16y ago

kingkilr 16y ago

Don't rely on the database to not put null in a column marked not nulls? That's like saying don't rely on C to not put a struct in a char*. It's got nothing to do with application logic.

ars 16y ago
He's wrong about that BTW. It doesn't put a NULL there, it puts zero for a number field, or a blank (0 length) string for a string field. Date fields also get 0 (00-00-0000). Edit: I get a downmod for this? The group think here is amazing. I suppose if I bash MySQL I'll get some upmods even if the bashing is incorrect?
- dagheti 16y ago
  Inserting a default value is even worse than inserting NULL. Now you can't tell what data was wrongly inserted if you tried to clean up the mess later.
  - ars 16y ago
    What? Have you ever programmed SQL? Because what you wrote doesn't make sense. No data is inserted wrongly - you are simply leaving out a field. There is no mess. Just an unused field. Are you thinking it's like csv where if you leave out a column all the others are shifted? It's not like that. The standard says if you leave out a column that does not have a default the SQL should return an error. Instead MySQL puts in a default (but only if you tell it too in the configuration). Putting a NULL in a non-NULL field would be much worse.
    - dagheti 16y ago
      Oh I agree they are both very bad. Putting NULL in a non-NULL field is totally wrong. However I really do think that inserting an unexpected default value is worse than inserting NULL into a NON-NULL field. The NULLs will cause problems, but they are problems you can see and resolve. The default values are silent errors that will corrupt your data and be very difficult to recover from in the future. You can only guess which data was wrongly inserted.
      - ars 16y ago
        I still don't see how it's possible to insert wrong data, or corrupt it?? There IS no data. How do you corrupt something that doesn't exist? And NULL doesn't help either. NULL is valid data, NULL is not a replacement for programming errors (which is what this is). This argument is pointless. People love to bash on MySQL, they look for the silliest things. The more popular something is the more people bash on it. I understand that, but at least bash on real problems? Like the transaction DDL - that's a real problem. This? This is nonsense. (It's actually a very useful - and optional - feature BTW.)
        Lewisham 16y ago
        I'm not sure you understand the use of NULL. NULL is not "default value" or "I don't care", NULL signifies "this might have a value, I just don't know what it is". There is a very significant difference between a payroll record which states your pay is "0" vs. NULL. If the database is putting in default values, you have no way of knowing whether the employee really did have a salary, but it was incorrectly inserted as NULL, or whether the employee is unpaid.
        ars 16y ago
        I understand NULL very well, and that's not the only use for a NULL. NULL also means "value does not exist", not just "value is unknown". For example if a student is not in a class, put NULL in the class id. NULL is perfectly valid data, and is not a replacement for a programming bug. And with mysql if your salary field is defined as accepting NULL then you will get a NULL in there. And to use your example if the field accepts NULL, you would also have no way of knowing if the salary was not negotiated vs a programming bug. If you want to argue the insert should fail, then fine, no problem. (And MySQL can do that.) But arguing that putting in NULL is better (in a field that does not accept NULL), is simply wrong. I'll say it again: NULL is not a replacement for a programming bug - NULL is valid data, and should not be used to find programming errors.
  - kls 16y ago
    Correct, NULL is NULL, if the system is designed that it has to have a default value it should at least scream at you "I can't do NULL, you are going to get a 0 here". Or better yet just error at design time, not run time, and say sorry put a value here I don't do NULL. Run-time errors are the worst kind of errors and it is little unexpected behaviors like that, that can create huge problem in a run time environment.
- deleted 16y ago
  [deleted]

Lewisham 16y ago

I don't see anywhere where he is calling for application logic. He's making sure that the data has integrity, and the database absolutely should be in charge of that. If your database can't enforce integrity, then you're in a very, very bad place. Putting in sane integrity checks to a database should be a priority. You can fix broken applications, but you can't fix broken data (especially if you collected it from users, sensors).

dasil003 16y ago

What if you have multiple applications talking to the same database?

derefr 16y ago
I think, in 2010, a "cloud-based web platform with a RESTful API" is 1990's "database." We don't let multiple applications talk to a single database; we stick a web server in front of the database and make them talk to that. If all the web API is doing is allowing CRUD on your data (with validation and some minimal triggered interactions) then, in effect, you're just creating your own, completely custom DBMS by doing this.
- mmt 16y ago
  in effect, you're just creating your own, completely custom DBMS by doing this. I've argued for years that this is what the "sharding" hype (and to some degree the current NoSQL hype) was mostly about. I'm not suggesting a custom DBMS can't possibly be the right answer, but it seems silly to layer it on top of something as heavyweight as a relational database.
  - randallsquared 16y ago
    If you don't use relational databases, you're throwing away decades of work into a problem that's very similar to the one you're trying to solve. If you have a brilliant new scheme that forces you to do that, fine, but throwing away finely tuned systems because complex and relatively opaque might not be the best first step.
    - mmt 16y ago
      Exactly. I would advocate not throwing any of it away, absent rare circumstances. I implied, though foolishly didn't state outright, that I believe a custom DBMS is only very rarely the best answer.
- dasil003 16y ago
  Yes, and this has a lot of advantages. One advantage is openness of APIs for mashups etc. But the big advantage driving the NoSQL/custom DB is of course scalability and performance. Incidentally performance was also a key consideration 40 years ago when everyone rolled their own custom data stores. The modern SQL database evolved as a response to all the problems that created, which by now are long forgotten. My point is not to throw the baby out with the bathwater. If you need massive scale then of course you need fresh approaches, but you are sacrificing a lot by abandoning a centralized DB. If your data is moderately sized, then you will be trading a lot of data integrity, queryability and flexibility by giving up an SQL databases. Certainly consumer web services of the type that are en vogue in silicon valley need new approaches. However the majority of applications out there are probably still best served primarily by a relational DB, especially when you consider the value of a byte of corporate data vs the value of a byte of facebook data.

masklinn 16y ago

There was nothing about application logic in the post. Quite the opposite in fact (transactional DDL and deferred constraints are things which have no reason to be handled in the applicative layer). It was only about the internal consistency of the data.

tomjen3 16y ago

I made the same argument to my database teacher. He pointed out that isn't going to do you much good when you have more than one application talking to your database, since you would then have to write the code to validate it in each application.

ryanpetrich 16y ago
Better yet, require that all applications call through some common API (such as a library or web service) to access the data.
- ora600 16y ago
  You can require this, but its tricky to enforce. Especially on DBAs.
- electrum 16y ago
  Why not put that API inside the database (i.e. stored procedures and views)?

ora600 16y ago

In any database comes a time where a developer or administrator will have to make manual changes to the data through an ad-hoc script. It can be straight sql or perl/php/ruby thing. The easier you'll make it on him not to cause horrible corruptions in the data by forgetting to update that additional table that depends on whatever he is updating - the better life will be. Triggers, foreign keys and constraints are all excellent ways to do just that.