SQL: does your app really need it? Google devs "battle" the backend

[Darren Dirt]

...that sounds dirty...
http://www.youtube.com/watch?v=rRoy6I4gKWU



basically a promo/tutorial for "The Cloud" using Google App Engine.
It's 45 minutes long, but Good News: there's the "transcript" feature in Youtube, if you want to skim the words to get a feel for what they discuss, debate, and demonstrate.

[Mr. Analog]

I really hate article hooks like this because the base assumption is that the app (whatever it is) is going to NEED a database.

Not every app needs a read/write storage medium, let alone a database.

I see this crap on tech sites all the time and it's mostly just to get people looking at some product or to inflate ad revenue.

To me it reads like "When you go on vacation what skies do you bring"... and I'm all like, what if I don't need skies at all?

LOL

[Darren Dirt]

To me it reads like "When you go on vacation what skies do you bring"... and I'm all like, what if I don't need skies at all?

LOL

Sounds like your vacation will be pretty...

:sunglasses:

VANILLA.

http://instantrimshot.com/classic/?sound=csi

[Mr. Analog]

Was that a @%&#ing, Vanilla Sky reference?

saflkd;adf

/sends darren into a google hole for 7 hours

[Thorin]

Not every app needs a read/write storage medium, let alone a database.

I think most pieces of software do require some sort of storage medium, as most pieces of software have some kind of output that we want to retain on a semi-permanent basis.  In no way am I saying it has to be a relational database, though.  It could just as easily be a file of some type, like a picture or an audio track or a word document or a save file.  I guess microcontroller code like what controls your microwave or washing machine doesn't need to store anything on a semi-permanent basis?  I'm having trouble of thinking of any other software that doesn't need to do that.

[Mr. Analog]

Some sites are static but deliver content based on runtime parameters, so yeah I guess you could say the hard drive itself is storage but there is no data created by the client to store, it's all transient.

[Darren Dirt]

I think most pieces of software do require some sort of storage medium, as most pieces of software have some kind of output that we want to retain on a semi-permanent basis.  In no way am I saying it has to be a relational database, though.  It could just as easily be a file of some type, like a picture or an audio track or a word document or a save file.  I guess microcontroller code like what controls your microwave or washing machine doesn't need to store anything on a semi-permanent basis?  I'm having trouble of thinking of any other software that doesn't need to do that.

I suspect the debate is really to cover both bases, i.e. "hey customers, just cuz you don't need to store fully-normalized RDBMS data for your app, doesn't mean you don't need to store less-structured data -- and we can help you with that too".

[Lazybones]

I think most pieces of software do require some sort of storage medium, as most pieces of software have some kind of output that we want to retain on a semi-permanent basis.  In no way am I saying it has to be a relational database, though.  It could just as easily be a file of some type, like a picture or an audio track or a word document or a save file.  I guess microcontroller code like what controls your microwave or washing machine doesn't need to store anything on a semi-permanent basis?  I'm having trouble of thinking of any other software that doesn't need to do that.

I suspect the debate is really to cover both bases, i.e. "hey customers, just cuz you don't need to store fully-normalized RDBMS data for your app, doesn't mean you don't need to store less-structured data -- and we can help you with that too".

There are also a lot of people out there that SHOULD be normalizing their data but do not.

I remember one DB I had to work with on Oracle non the less where the damn table I was getting data from had a huge number of columns with related data in it instead of separate tables ... I can't imaging the hardware they probably needed behind it as there was so much text in it.

[Mr. Analog]

DB normalization (or rather, the lack of) is my number one pet peeve.

I don't know what people think they are saving themselves sometimes, I have a DB where we have an Entity that doesn't exist, it is a logical entity that SHOULD exist but is faked through a variety of views.

At which point I say why not just add a gd table? IT'S NOT THAT MUCH WORK...

In conclusion, people are dumb.

[Thorin]

In conclusion, people are dumb.

FAMOUS MIB QUOTE: http://youtu.be/kkCwFkOZoOY?t=10s.  I can't believe how serious this point was, given the rest of the movie.

DB normalization (or rather, the lack of) is my number one pet peeve.

I don't know what people think they are saving themselves

There are developers who think every app needs a properly-normalized database which is not true.  However, there are a surprisingly-large number of developers who feel uncomfortable with relational databases and therefore try to do everything in code.

Imagine code that has to get parent records and detail records.  There's one query for the parent records, and then a separate query for the detail record for each parent records.  If there's 1,000 parent records, this is 1,001 queries.  I understand that we're not supposed to optimize early because we're probably optimizing the wrong thing, but hey, 1,001 queries is pretty much always worse than getting the database server to do some work and then respond with a combined set of data.

And then I end up getting to debug why the code times out after three minutes.

[Mr. Analog]

Woo don't even get me started on structural theory. As most of us who graduated from CS programs in college know you just get the most basic of tools to get you started in the industry and that you have to learn more as you go along, this doesn't seem to be the case with DB design and very often I see people design structures that "work" because of familiarity, not because it's the best fit.

Obviously there are pros and cons to weigh for every choice but man, 99 times out of 100 when you see a parent / child relationship in the same table it's using the adjacency model (there is a parent id which is a FK back to the table's PK), this is an easy concept to grep for most people THE PROBLEM IS that it's about the least efficient way of reading this kind of data (easy to write though). The other model, the model designed for read performance and for easy selection is known as nested set theory (where there are two pointers giving you the range where an entity lives within a structure), the baggage it carries is that it's slightly more difficult to write update statements for (because you have to update all the relational linkages as you insert or delete data).

So if you have a parent/child relationship that is frequently updated and you don't have to scan entire branches and leaves in one fell swoop then it works pretty well, but if you want to look at more than one branch/leaf in a single operation and your structure doesn't really get updated all that often there are options, options that a lot of people don't know exist, so don't bother looking up.

When you see stuff like this in action it's a beautiful thing, if you don't know they exist you find people pounding square pegs into round holes all the damn time like you say.

We had one guy working on our project who was quite adept at front end stuff but didn't know jack about our DAL or SQL, so what he would do is create phoney entities and load them by only going as far as the controller (not even the Model!!), what this means is he would take existing calls for data in the controller and cobble them together so he could get what he wanted. When I started profiling activity I was gobsmacked, there were thousands of calls happening to collect metadata that was then being massaged into custom entities living in the controller, so it was CPU heavy on the DB, CPU heavy on the model and memory intensive on the controller. AND ALL THIS WAS TO AVOID CREATING A SINGLE JOIN TABLE and updating a view.

Suffice to say I got rid of that code as fast as I could and replaced it with something that would work in a single DB operation and passed directly to the controller through new logical models that ACTUALLY REPRESENTED THE ENTITIES BEING USED.

Not only did it make our app better but it made debugging that @%&# a million times easier.

/rant

[Thorin]

But no comment on my MIB quote

I think what you're talking about is different implementations for ORMs.  I was speaking specifically about code that reads from the database and loads into objects.  The SQL that gets run there and the connections that get made to the database there.  In an ORM that's all hidden from the developer.

[Mr. Analog]

Yeah, ORM I don't care about if the DB design is @%&#ed from the start, I guess that's the point I'm trying to make, all these layers don't mean squat if one is broken before you start.

Then people who don't know any better start trying to make bricks without straw... it hurts my brain.

The MIB quote is good, I see it nearly every day, there's a duder on /. who has it as his sig.

[Darren Dirt]

Yeah, ORM I don't care about if the DB design is @%&#ed from the start, I guess that's the point I'm trying to make, all these layers don't mean squat if one is broken before you start.

Then people who don't know any better start trying to make bricks without straw... it hurts my brain.

"heavy reliance on ORM software has been cited as a major factor in producing poorly designed databases."
^ http://en.wikipedia.org/wiki/Object-relational_mapping

[Mr. Analog]

For the most part I agree.

One size fits all is great... until it isn't.