9 ms·

SQL has the great side effect of creating structures that future employees can understand. Its a set of tables with relationships. Given these you can quickl

SQL has the great side effect of creating structures that future employees can understand. Its a set of tables with relationships. Given these you can quickly inspect the structures independent of the programming language dejour. With a few commands a new employee can understand: business logic, hr, billing, reporting, and other major backbone principals in a matter of hours. On the other hand, I have been at companies that jumped on the noSQL / ORM / wrap it until you can't wrap it / hide sql (rails). When new employees show up... well... its a bunch of semi/no structured stuff spread across thousands of lines of specific logic.

forgotpwtomain 10y ago
Rails tried to hide SQL so much that one of the consequences was not supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations.
- jgeraert 10y ago
  Seeing this as well in proprietary software sold by one of the biggest erp software vendors... And it's still not fixed and likely won't be fixed anytime soon.
  - mannykannot 10y ago
    The relational model provides a pretty general and unified way to represent information, together with a cohesive and powerful set of primitives for working with it, yet many systems architects insist on hiding it behind an ad-hoc interface that looks like a throwback to pre-relational days.
  - moron4hire 10y ago
    There is a perception amongst lower programmers that FKs are only a thing you enable in production, that they are an impediment to rapid iteration. I mean, it's completely wrong, I know that. But try being the new guy telling a team of 10 people that. There is also a perverse corruption of "if it ain't broke, don't fix it" that goes on. If you can spend 100 hours manually validating every relationship in your application code, that's 100 hours you can put into your estimate and you know you can complete. If you only have a cursory understanding of SQL, then "learn more about FKs and implement them across the DB" seems like a big, unknowable blob of time that is impossible to estimate. It doesn't matter that it might only be 5 hours of work, at least we can be certain about 100 hours and bill the client for it. Finally, there are a small set of things that are fundamentally wrong about all modern RDBMS implementations. For example: it's nonsensical to have a foreign key that isn't indexed. You always want an index on foreign keys, there is never a scenario where you don't want them. But while primary keys are indexed by default, foreign keys are not. And those sorts of things give the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.
    - mattbroekhuis 10y ago
      I have found out the hard way that foreign keys are indexed by default in some implementations including MySQL innodb. Not in oracle. My experience has been the opposite of yours -- I've heard of disabling foreign keys in production only for performance reasons, but not the other way around. I like them turned on all the time as well. Valuable safety rail
    - aidos 10y ago
      Curious, this doesn't fit my mental model: it's nonsensical to have a foreign key that isn't indexed I guess you normally want indexes on parent - child tree relationships (book -> chapter), but you don't want them all the time. What about when you have an 'article' with a 'status and a 'category' and you only ever find all articles by combination of category and status? In that case you'd be maintaining 3 indexes, category, status and status_category, but the only one you'd need would be status_category. Indexes are a tool to to allow for optimising lookups while foreign keys are a tool to allow you to keep your data consistent.
      - moron4hire 10y ago
        I don't see how your example doesn't require an index, specifically because, as you said, indexes are a tool to allow for optimizing lookups. I imagine that maybe you're suggesting you first query the DB for the ID of the combined status_category, and then query the article by that ID. That's not a good idea, for a couple of reasons. First, you're making two round trips to the DB when you could, with no additional effort (just effort in a different place) be doing one. Second, you've introduced a data race condition. If someone deletes that status_category after you've queried for it but before you've queried the articles, you aren't going to get the results you want. It would be better to do a join across articles to status_category to status and category, then query based on the status and category values you want. Without an index on the FKs between status_category and status and category, a relatively small table can have a big impact on query performance. Finally, while I know your example is arbitrary, it's a little hard to argue against a design that is probably wrong. I doubt the suggested schema for articles and categories is a good one. If I argue "you should never have to arbitrarily subtract '1' from a result just to get the results you want", it would not be a good counterargument to say, "yes, but sometimes you want to add 2 and 2 and get 5, so then you need to subtract 1". The problem isn't where you see it. FKs aren't just a consistency tool. Consistency and referential integrity are features that results from having an FK, but the FK is a signal that data can be searched in a certain way.
- jmfurlott 10y ago
  This fact alone, after several years of changing schema and migrations, led to us having major rewrites and porting over to much more hands off approach. It's an understatement how much AR has led to just confusion especially with new employees!
- elsurudo 10y ago
  That's true, but you could always either create them yourself in SQL in a migration, or use a gem like foreigner (as I did). Always use FKs...
- ad-hominem 10y ago
  > Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations. This is a failure of the programmers. Rails itself didn't really support foreign keys, but relationships did have an option to destroy any child tuples when the parent is destroyed (culling orphans). This sounds like it wasn't enabled when it should have been.
- geebee 10y ago
  I regularly use raw sql in rails. ORMs are fine for convenience, but for me, they never make or break an app. They can save you some typing on very basic stuff, but if you find you're writing complex ORM code, I think it's a good idea to just use sql directly.
pjmorris 10y ago
"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious." - Fred Brooks, The Mythical Man Month.
- mannykannot 10y ago
  I have seen at least one case where I could ascribe no meaning to several of the tables in a database. To be fair, seeing the code did not help either, until it became apparent that it was all an attempt to work around a fundamental error. The creator of this mess was blaming the users for it not working, and I was happy to refute that claim and exonerate the users.
- vog 10y ago
  Note that "tables" in this statement had a different meaning than today. That one wasn't about relational databases, it was about tables as in-memory data structures. In today's words, "flowcharts" means "code" and "tables" means "data structures". I don't care if it is: - tables in a relational database - nested structs (records) and lists - nested dictionaries (hash tables) and lists - JSON - XML - ... whatever What I do care is that I can see the data structures, and not just the code. Static typing often gets that job done fairly well. If you don't use that, please use at least type annotations. This is the most important part of your documetation, and the compiler ensures it remains correct over time. Most of the time, this (+ the function name) is the only documentation I really need.
  - deleted 10y ago
    [deleted]
- scarface74 10y ago
  Show me your well structured code and well named unit tests and I won't need your flow charts. Show me your well defined normalized tables and I have all I need. But for the love of $entity please don't show me tons of business logic in stored procedures. I actually won't work for a company that expects me to build or maintain a product based on stored procs.
brudgers 10y ago
Exposing data structures in a human readable form was probably a necessity because SQL slotted itself into a space where COBOL lived...and one of the features of COBOL was its intent to create readable data processing programs. Like SQL, COBOL was generally a very reasonable alternative when practical engineering and business considerations were all on the table (e.g. mature well documented tools and the availability of employable candidates).
- mkesper 10y ago
  Before databases you had only flat files or VSAM (and before that, ISAM) for your data. Good luck debugging your data in a VSAM cluster. https://en.wikipedia.org/wiki/VSAM https://en.wikipedia.org/wiki/VSAM
  - SmellTheGlove 10y ago
    Thankfully, SAS handles VSAM reasonably well. Don't ask me why I know this, or why I need to continue to know this.
  - brudgers 10y ago
    My apologies for not being clear. By "data structures" I meant data structures and by implication that COBOL provided an abstraction over bytes in memory or on persistent storage media. The premise of my statement is that SQL is also an abstraction over bytes in memory or on persistent storage media in similar use cases.
jrochkind1 10y ago
You put your business logic in the rdbms? As constraints and triggers and stored procedures and such? There are advantages to that, but pretty big disadvantages too. For maintaining and developing a non-trivial app, SQL, or a particular vendor's SQL variant, is definitely not my preferred platform. I definitely don't find that something easy to understand coming to an existing project that's been developed like that. I guess it _could_ be a matter of taste and experience, if it is yours!
- hackits 10y ago
  Most of the time when business logic is put into triggers it turn into a disaster. Even Oracle recommends only putting simple logic within triggers and for the most part should only be used for auditing purposes. For example too many times I've updated a record with a new column value only to find out that the value I've updated because of some trigger caused the value to be set to null.
- cryptonector 10y ago
  One advantage: you get all the write concurrency your RDBMS can give you for your transactions. Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time. I can't think of disadvantages to putting your business logic in the RDBMS. Can you elaborate?
  - jrochkind1 10y ago
    > Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time. Geez, I think you've seen some pretty terrible ORMs. I don't know of any mature and popular ORMs that do that. If they do, I have no idea why they are popular or what makes anyone consider them mature. ORMs that don't do that include: ActiveRecord, Sequel, Hibernate, Core Data, SQLAlchemy, etc.
- cr0sh 10y ago
  I could be wrong, but I think the main reason business logic is put into the DB (and provided that the "users" of the DB are also segregated properly), is to present a cohesive view to all users of the DB. Too often, when you have a "wide open" DB and multiple "users" of that DB (think groups within an org) decide to query it (whether just for reading, or worse, updating), the queries can often turn out to be radically different for the same business logic. Maybe one development group thinks that a query should be done one way, while another thinks that to get the same data it should be done another, and now you have two or more groups disseminating data to other groups in totally different ways. You may ask why the organization has multiple development groups, but it occurs. I worked for one company I won't name because it isn't important - as a web developer in their marketing department; our group was considered as separate from the IT group, which handled the main "DB" which was based around AS/400 systems and Apache SOLR - we worked together as best as possible, but also bumped heads, in that they wanted us to only work with their "DB" (I don't really consider SOLR to be a DB, though it can and is used like one by a variety of orgs) through their interface (which at times didn't work like they documented it - and many times changed without us knowing about it until our stuff broke mysteriously - usually around the end-of-year holiday push) - whereas we needed to store a lot of the stuff "nearline" in a MySQL DB (we used MySQL, PHP, and AWS for much of our development) to make things more responsive for our end users (ie - the people buying the products the company made off the websites we were creating). In essence, we a bit of a "split personality" going on - but our main boss was only two levels removed from the CEO of the company, so our stuff generally was tolerated - but it is a similar situation. Where you have multiple dev groups (whether by design or because "ad-hoc" things occur - like Bob in accounting figuring out how to use ODBC with Excel macros to query the database for data for his and other departments), you can have this kind of chaos. When the DB is heavily controlled by IT, with the only "views" of it through tightly controlled business logic which is part of the DB, this kind of difference in data can be controlled. But it does have many more downsides, which also means it probably shouldn't be used or done in that manner. Instead, the interface should probably be through a single interface (RESTful or similar), with the business logic in the code of that interface, and only the barest needed other logic in the DB to tie it together. Provided that the security on the DB is tightly controlled, and the only way other groups can access the data is via the exposed interface API, you can achieve the same results I think. In a way, that's how we had the access at that one company - we had a RESTful interface API to the backend SOLR store; we could send a formatted "query object" and get back one or more "records" as a stream of data (we'd then usually take the data, parse through it and store parts of it into our MySQL DB - because the query/response time of that SOLR DB was horrible from a web development perspective; I don't think this was the fault of IT, but rather the fact that the datastore on the backend was vast, holding information about products dating back 60 or more years - I'm sure there was likely some COBOL in the mix somewhere). That did have the downside of the fact that if we wanted a particular means to query for something that didn't exist in the existing API, we either had to make due with what we could do "locally" (thru code and/or mysql "buffering"), or we had to put in a request for a change to the IT group (which may or may not get accepted, and might take weeks for the turnaround time before we could use it). Furthermore, as I mentioned before, there were more than a few times that we rolled out a particular feature, only to have the website(s) that relied on that feature break because the backend API changed "behind our backs" (and we usually saw this over a holiday period, when our sales would peak of course). In many cases, we couldn't do anything about this (not even storing the SOLR information - it was too vast, plus there was a nagging idea that if we tried that someone's head would roll for not using the implemented interfaces and data that already existed - we could "buffer" or "cache" things, but we couldn't wholesale transfer the data over).
deleted 10y ago
[deleted]
collyw 10y ago
Personally I find SQL to be one of the easiest languages to read (when reading code from other developers).
1ris 10y ago
This is not true for any non-trivial database. I had to learn this. Business logic still has to be understood, there is still lot's of domain knowlege how this is indented to be used and how not, and in my case there was still lot's of ambigous, poorly documented VBA-Code to be understood. I'm not aware of a database without a frontend of some kind.

forgotpwtomain 10y ago

Rails tried to hide SQL so much that one of the consequences was not supporting proper table level constraints (FK) till AR ~4.2 or so. Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations.

jgeraert 10y ago
Seeing this as well in proprietary software sold by one of the biggest erp software vendors... And it's still not fixed and likely won't be fixed anytime soon.
- mannykannot 10y ago
  The relational model provides a pretty general and unified way to represent information, together with a cohesive and powerful set of primitives for working with it, yet many systems architects insist on hiding it behind an ad-hoc interface that looks like a throwback to pre-relational days.
- moron4hire 10y ago
  There is a perception amongst lower programmers that FKs are only a thing you enable in production, that they are an impediment to rapid iteration. I mean, it's completely wrong, I know that. But try being the new guy telling a team of 10 people that. There is also a perverse corruption of "if it ain't broke, don't fix it" that goes on. If you can spend 100 hours manually validating every relationship in your application code, that's 100 hours you can put into your estimate and you know you can complete. If you only have a cursory understanding of SQL, then "learn more about FKs and implement them across the DB" seems like a big, unknowable blob of time that is impossible to estimate. It doesn't matter that it might only be 5 hours of work, at least we can be certain about 100 hours and bill the client for it. Finally, there are a small set of things that are fundamentally wrong about all modern RDBMS implementations. For example: it's nonsensical to have a foreign key that isn't indexed. You always want an index on foreign keys, there is never a scenario where you don't want them. But while primary keys are indexed by default, foreign keys are not. And those sorts of things give the anti-RDBMS crowd enough of a foot-hold to argue for continued ignorance.
  - mattbroekhuis 10y ago
    I have found out the hard way that foreign keys are indexed by default in some implementations including MySQL innodb. Not in oracle. My experience has been the opposite of yours -- I've heard of disabling foreign keys in production only for performance reasons, but not the other way around. I like them turned on all the time as well. Valuable safety rail
  - aidos 10y ago
    Curious, this doesn't fit my mental model: it's nonsensical to have a foreign key that isn't indexed I guess you normally want indexes on parent - child tree relationships (book -> chapter), but you don't want them all the time. What about when you have an 'article' with a 'status and a 'category' and you only ever find all articles by combination of category and status? In that case you'd be maintaining 3 indexes, category, status and status_category, but the only one you'd need would be status_category. Indexes are a tool to to allow for optimising lookups while foreign keys are a tool to allow you to keep your data consistent.
    - moron4hire 10y ago
      I don't see how your example doesn't require an index, specifically because, as you said, indexes are a tool to allow for optimizing lookups. I imagine that maybe you're suggesting you first query the DB for the ID of the combined status_category, and then query the article by that ID. That's not a good idea, for a couple of reasons. First, you're making two round trips to the DB when you could, with no additional effort (just effort in a different place) be doing one. Second, you've introduced a data race condition. If someone deletes that status_category after you've queried for it but before you've queried the articles, you aren't going to get the results you want. It would be better to do a join across articles to status_category to status and category, then query based on the status and category values you want. Without an index on the FKs between status_category and status and category, a relatively small table can have a big impact on query performance. Finally, while I know your example is arbitrary, it's a little hard to argue against a design that is probably wrong. I doubt the suggested schema for articles and categories is a good one. If I argue "you should never have to arbitrarily subtract '1' from a result just to get the results you want", it would not be a good counterargument to say, "yes, but sometimes you want to add 2 and 2 and get 5, so then you need to subtract 1". The problem isn't where you see it. FKs aren't just a consistency tool. Consistency and referential integrity are features that results from having an FK, but the FK is a signal that data can be searched in a certain way.
jmfurlott 10y ago
This fact alone, after several years of changing schema and migrations, led to us having major rewrites and porting over to much more hands off approach. It's an understatement how much AR has led to just confusion especially with new employees!
elsurudo 10y ago
That's true, but you could always either create them yourself in SQL in a migration, or use a gem like foreigner (as I did). Always use FKs...
ad-hominem 10y ago
> Just take a look at the DB of a company using Rails in production: hundreds of keys to non-existent relations. This is a failure of the programmers. Rails itself didn't really support foreign keys, but relationships did have an option to destroy any child tuples when the parent is destroyed (culling orphans). This sounds like it wasn't enabled when it should have been.
geebee 10y ago
I regularly use raw sql in rails. ORMs are fine for convenience, but for me, they never make or break an app. They can save you some typing on very basic stuff, but if you find you're writing complex ORM code, I think it's a good idea to just use sql directly.

pjmorris 10y ago

"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious." - Fred Brooks, The Mythical Man Month.

mannykannot 10y ago
I have seen at least one case where I could ascribe no meaning to several of the tables in a database. To be fair, seeing the code did not help either, until it became apparent that it was all an attempt to work around a fundamental error. The creator of this mess was blaming the users for it not working, and I was happy to refute that claim and exonerate the users.
vog 10y ago
Note that "tables" in this statement had a different meaning than today. That one wasn't about relational databases, it was about tables as in-memory data structures. In today's words, "flowcharts" means "code" and "tables" means "data structures". I don't care if it is: - tables in a relational database - nested structs (records) and lists - nested dictionaries (hash tables) and lists - JSON - XML - ... whatever What I do care is that I can see the data structures, and not just the code. Static typing often gets that job done fairly well. If you don't use that, please use at least type annotations. This is the most important part of your documetation, and the compiler ensures it remains correct over time. Most of the time, this (+ the function name) is the only documentation I really need.
- deleted 10y ago
  [deleted]
scarface74 10y ago
Show me your well structured code and well named unit tests and I won't need your flow charts. Show me your well defined normalized tables and I have all I need. But for the love of $entity please don't show me tons of business logic in stored procedures. I actually won't work for a company that expects me to build or maintain a product based on stored procs.

brudgers 10y ago

Exposing data structures in a human readable form was probably a necessity because SQL slotted itself into a space where COBOL lived...and one of the features of COBOL was its intent to create readable data processing programs. Like SQL, COBOL was generally a very reasonable alternative when practical engineering and business considerations were all on the table (e.g. mature well documented tools and the availability of employable candidates).

mkesper 10y ago
Before databases you had only flat files or VSAM (and before that, ISAM) for your data. Good luck debugging your data in a VSAM cluster. https://en.wikipedia.org/wiki/VSAM https://en.wikipedia.org/wiki/VSAM
- SmellTheGlove 10y ago
  Thankfully, SAS handles VSAM reasonably well. Don't ask me why I know this, or why I need to continue to know this.
- brudgers 10y ago
  My apologies for not being clear. By "data structures" I meant data structures and by implication that COBOL provided an abstraction over bytes in memory or on persistent storage media. The premise of my statement is that SQL is also an abstraction over bytes in memory or on persistent storage media in similar use cases.

jrochkind1 10y ago

You put your business logic in the rdbms? As constraints and triggers and stored procedures and such? There are advantages to that, but pretty big disadvantages too. For maintaining and developing a non-trivial app, SQL, or a particular vendor's SQL variant, is definitely not my preferred platform. I definitely don't find that something easy to understand coming to an existing project that's been developed like that. I guess it _could_ be a matter of taste and experience, if it is yours!

hackits 10y ago
Most of the time when business logic is put into triggers it turn into a disaster. Even Oracle recommends only putting simple logic within triggers and for the most part should only be used for auditing purposes. For example too many times I've updated a record with a new column value only to find out that the value I've updated because of some trigger caused the value to be set to null.
cryptonector 10y ago
One advantage: you get all the write concurrency your RDBMS can give you for your transactions. Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time. I can't think of disadvantages to putting your business logic in the RDBMS. Can you elaborate?
- jrochkind1 10y ago
  > Too many ORMs I've seen just have a Big Lock, leaving you with just one writer at a time. Geez, I think you've seen some pretty terrible ORMs. I don't know of any mature and popular ORMs that do that. If they do, I have no idea why they are popular or what makes anyone consider them mature. ORMs that don't do that include: ActiveRecord, Sequel, Hibernate, Core Data, SQLAlchemy, etc.
cr0sh 10y ago
I could be wrong, but I think the main reason business logic is put into the DB (and provided that the "users" of the DB are also segregated properly), is to present a cohesive view to all users of the DB. Too often, when you have a "wide open" DB and multiple "users" of that DB (think groups within an org) decide to query it (whether just for reading, or worse, updating), the queries can often turn out to be radically different for the same business logic. Maybe one development group thinks that a query should be done one way, while another thinks that to get the same data it should be done another, and now you have two or more groups disseminating data to other groups in totally different ways. You may ask why the organization has multiple development groups, but it occurs. I worked for one company I won't name because it isn't important - as a web developer in their marketing department; our group was considered as separate from the IT group, which handled the main "DB" which was based around AS/400 systems and Apache SOLR - we worked together as best as possible, but also bumped heads, in that they wanted us to only work with their "DB" (I don't really consider SOLR to be a DB, though it can and is used like one by a variety of orgs) through their interface (which at times didn't work like they documented it - and many times changed without us knowing about it until our stuff broke mysteriously - usually around the end-of-year holiday push) - whereas we needed to store a lot of the stuff "nearline" in a MySQL DB (we used MySQL, PHP, and AWS for much of our development) to make things more responsive for our end users (ie - the people buying the products the company made off the websites we were creating). In essence, we a bit of a "split personality" going on - but our main boss was only two levels removed from the CEO of the company, so our stuff generally was tolerated - but it is a similar situation. Where you have multiple dev groups (whether by design or because "ad-hoc" things occur - like Bob in accounting figuring out how to use ODBC with Excel macros to query the database for data for his and other departments), you can have this kind of chaos. When the DB is heavily controlled by IT, with the only "views" of it through tightly controlled business logic which is part of the DB, this kind of difference in data can be controlled. But it does have many more downsides, which also means it probably shouldn't be used or done in that manner. Instead, the interface should probably be through a single interface (RESTful or similar), with the business logic in the code of that interface, and only the barest needed other logic in the DB to tie it together. Provided that the security on the DB is tightly controlled, and the only way other groups can access the data is via the exposed interface API, you can achieve the same results I think. In a way, that's how we had the access at that one company - we had a RESTful interface API to the backend SOLR store; we could send a formatted "query object" and get back one or more "records" as a stream of data (we'd then usually take the data, parse through it and store parts of it into our MySQL DB - because the query/response time of that SOLR DB was horrible from a web development perspective; I don't think this was the fault of IT, but rather the fact that the datastore on the backend was vast, holding information about products dating back 60 or more years - I'm sure there was likely some COBOL in the mix somewhere). That did have the downside of the fact that if we wanted a particular means to query for something that didn't exist in the existing API, we either had to make due with what we could do "locally" (thru code and/or mysql "buffering"), or we had to put in a request for a change to the IT group (which may or may not get accepted, and might take weeks for the turnaround time before we could use it). Furthermore, as I mentioned before, there were more than a few times that we rolled out a particular feature, only to have the website(s) that relied on that feature break because the backend API changed "behind our backs" (and we usually saw this over a holiday period, when our sales would peak of course). In many cases, we couldn't do anything about this (not even storing the SOLR information - it was too vast, plus there was a nagging idea that if we tried that someone's head would roll for not using the implemented interfaces and data that already existed - we could "buffer" or "cache" things, but we couldn't wholesale transfer the data over).

deleted 10y ago

[deleted]

collyw 10y ago

Personally I find SQL to be one of the easiest languages to read (when reading code from other developers).

1ris 10y ago

This is not true for any non-trivial database. I had to learn this. Business logic still has to be understood, there is still lot's of domain knowlege how this is indented to be used and how not, and in my case there was still lot's of ambigous, poorly documented VBA-Code to be understood. I'm not aware of a database without a frontend of some kind.