Hacker News Reader

Comment by mixologic | Hacker News Reader

Keep in mind that preserving space was of the utmost of importance in 1982. Eliminating data redundancy was considered the utmost of design, eschewing maintainability, intelligibility, and real application behaviors.

Please do not allow your data model designs to be artificially constrained by the requirements of the past.

View on HN

Wow, what an incredibly dense thing to presuppose and advice others that the idea of data normalisation is antiquated because the primary purpose, when it was "thought of", was to save disk space!

Data normalisation is all about correctness - mathematical correctness. It's about reducing inconsistencies in your abstracted domain model so that you do not suffer side effects when using your data (such as in your programs). It is not optional.

This is poor advice to others. Especially if "the adviced" are uninformed, new or finding the subject hard.

Normalization might actually take up more space, e.g. if you have to break out a column into a separate table and introduce a new primary key.

The reason for normalization is to preserve integrity of the data. If the same information occurs multiple times, it is possible to change it in one place and not the other, thereby introducing inconsistent data. This might have disastrous results depending on the application.

As far as I know, data integrity is still pretty important even today.

Redundancy should be a cause for concern regardless of the amount of storage available. Redundancy might be used deliberately eg. for caching or robustness, but even the you usually want to have one canonical version of the data.

>As far as I know, data integrity is still pretty important even today.

Tell that to the NoSQL hipsters.

For the love of (insert small thing here). NoSQL is not necessarily about throwing data integrity out the window. It's about re-organizing your data so that the things you care to be integrated are, such as a document with a sub-document or list. Pretty much every data store promotes some kind of data integrity.

It might not be ACID data integrity. It might not care to enforce referential integrity outside of the document, or value, or whatever the modeling paradigm is. There is still integrity. If you don't like that, fine. Fair enough. Some might be okay with such a system. Name calling is of little benefit. It is a form of violence. "Violence is the last refuge of the incompetent" - Isaac Asimov.

If you would like to learn more about the new data stores, see my short book, New Data: a Field Guide. https://leanpub.com/NewDataAFieldGuide. It's target at managers and other with little time to get a good idea.

>> Name calling is of little benefit. It is a form of violence. "Violence is the last refuge of the incompetent" - Isaac Asimov.

I hope you saw the irony in that.

It's ironic in the Alanis Morissette way.

However, it was more apt against the parent's comment. It did not say why NoSQL users are problematic, or even wrong. It just flat out lambasts anyone who walks away from the orthodoxy of ACID-SQL with an epithet. It truly was an incompetent comment.

I'm using ArangoDB on my project. My reason is that honestly what I'm doing really is document-y. I could use MySQL or PostGRE, but I'm opting for something different for the sake something different. I do want to have integrity, though. Arango has integrity at the document level as well as distributed transactions. It doesn't enforce references though. I'm fine with that.

What annoys me is when Internet commentator attack something new without at least a cursory show of reasoning.

> PostGRE

It's called Postgres, or PostgreSQL, by the way.

>> It's ironic in the Alanis Morissette way.

No, it was calling name callers incompetent, which caused an unintended self reference :-) I suppose it's not irony, but I don't think it's as far from it as Alanis.

When the internet annoys me, I try to hit the back button. Though sometimes, I just have to turn it off.

[Disclaimer: this was written instead of dumping gasoline on the fire with the first version.]

>Name calling is of little benefit. It is a form of violence.

No it is not:

the use of physical force to harm someone, to damage property, etc.

"I don't like X" does not mean you can lie about what X is to try to convince others not to like it too.

That's not what the parent did. He called them hipsters. Hipsters, at least in the HN circles, is not a complement. It's a disparaging term. He called them hipster without any force of argument.

Also, definition 5: rough or immoderate vehemence, as of feeling or language. http://dictionary.reference.com/browse/violence

>That's not what the parent did

No, it is what you did. You dislike what he said, and rather than address it factually, you lied about it. Calling people hipsters is not violence.

Two questions: 1) where did I lie? 2) How is insulting not a form of violence? To critique a work as incompetent is not to disparage the person. It is to say they didn't think something through.

In my post I said the NoSQL has a different definition of integrity. As a result, one has to use its terms in order to come to a conclusion about the validity of its claims. I took offense at the OP equivocating on integrity and then insulting people.

>where did I lie?

Claiming the thing you don't like (calling people hipsters) is violence.

>How is insulting not a form of violence?

How is foot not a color?

Taking Asimov quotes out of context is a form of violence.

Eliminating data redundancy isn't about saving space and can increase space requirements depending on the data. It's really just the database equivalent of the DRY[1] principle. Which is itself about maintainability, intelligibility, and real application behaviors. Although I will admit that 4th and 5th forms can trade intelligibility for flexibility.

[1] http://en.wikipedia.org/wiki/Don%27t_repeat_yourself

The brain-dead perception that "relationships are bad" came from the old brain-dead MySQL query optimizer (which has since been fixed, as far as I know). They did actually make things universally slower in that database engine and were a universally bad idea if you were using that specific database engine.

Relationships (and by association the normal forms) provide extremely important information/hints to the query optimizer and allows it to do stuff like join elimination[1]. It is my belief that SQL's bad name came from the widespread use of MySQL's partially functional engine and the poor performance caused by the lack of a proper query optimizer.

Aside: sometimes normal forms do cause too much additional overhead during queries, which is when you can use materialized views[2].

Not to mention that a normalized database is ripe for translation into analysis forms (cubes etc.), and that advanced tooling generally has a better understanding of normalized databases.

Basically, there are many more reasons (above the valid ones that you give) to have normalized forms and describe referential integrity.

[1] http://antognini.ch/2010/01/join-elimination/ [2] http://en.wikipedia.org/wiki/Materialized_view

>came from the old brain-dead MySQL query optimizer (which has since been fixed, as far as I know).

It came from the fact that mysql doesn't support joins properly. I do not believe this has been fixed, as far as I know it still only supports nested loop joins.

Yep, choosing the correct joining method is one of the tasks that a query optimizer (a.k.a. join engine) performs. I think someone around here pointed me toward a document that indicated that there was, at the very least, an experimental competent query optimizer in the MySQL codebase.

Even then it's a far cry away from "the big guns." Things like statistics, DPVs, etc. simply aren't (edit: or weren't) present in MySQL: which are more examples of a critical components of efficient database design at massive load scales.

TLDR; MySQL simply isn't competitive, it's being improved (especially MariaDB) but is a long ways off.

MySQL's optimizer isn't best of breed but your comment is massively ignorant. MySQL supports transactions, row level locking, nested SQL, and even replication -- how would you ever get the idea that it only ever just supported nested loop joins?

He's referring specifically to the features within the joining engine, not MySQL as a whole.

[Edit] I'm refuting that this:

> MySQL supports transactions, row level locking, nested SQL, and even replication

is relevant in the context of what he said.

MySQL always did more than nested loop joins -- which is literally the worst kind of join. It's the join you get if you have no indexes on anything in any DBMS. I fail to see what your reply here is trying to refute.

Even your comment, stating that MySQL doesn't maintain statistics, it's totally wrong and easily checked with 5 seconds of Googling.

To your edit: The idea that a DBMS would support so many advanced features and do nested loop joins is laughably ridiculous.

I'm no MySQL expert, but the docs for 5.6 specifically state: "MySQL resolves all joins using a nested-loop join method." [0]

Can you provide documentation on other join methods?

[0] - http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

MySQL sort-merge joins, it's right in that documentation, but it calls everything out as optimizations on nested-loop.

http://www.xaprb.com/blog/2013/10/01/mysql-isnt-limited-to-n...

It doesn't do hash joins which is the biggest limitation but MySQL is pretty good a joins and certainly was never bad enough to warrant it's reputation. It's toolbox is smaller than other RDBMS products but it's not empty.

You claim that MySQL always did more than nested loop joins, and yet the features cited in that blog post were introduced in version 5.6, which was released in 2013... Several years after the other advanced features that supposedly preclude MySQL from doing something as stupid as nested loop joins.

> The idea that a DBMS would support so many advanced features and do nested loop joins is laughably ridiculous.

Correct. MySQL is laughably ridiculous.

No, my comment is a simple statement of fact. None of what you just mentioned is even remotely relevant. I get the idea that it only supports nested loop joins because it only supports nested loop joins. "MySQL resolves all joins using a nested-loop join method": http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

In my experience of you don't at least adhere to third normal form, you eventually regret it. Normalized data is more flexible when you need to extend a data model, at the cost of a little more effort during initial development.

I agree with you on the third normal form, but let's consider the fifth too. In TFA the example is agents working for car makers. Let's use a more realistic example: agents working for car dealers.

So Frank sells cars for Festive Ford. We store that in fifth normal form by saying that Frank sells cars and Frank works at Festive Ford, and Festive Ford sells cars.

But one day Eve walks in to Festive Ford and walks to trade in her Eagle. Frank cuts her a sweetheart deal and Eve drives away in a brand new Focus. Festive is now left with an Eagle to sell. Maybe we'll add a record saying that Festive sells Eagle cars too, though that seems silly when they've only ever had one to sell, and Eagle is out of business for a decade already. And even if we do that, Frank is a Ford man, so if he manages to sell the Eagle, we'll need to add a record for Frank being an Eagle salesman too.

After a few years of this, everyone is selling every brand according to the database, and the business people wonder why the website says Festival AMC-BMW-Chrysler-Dodge-Eagle-Ford-GMC-Honda-Isuzu-Jeep-Kia-Lexus...oh you silly programmers!

That's not what the database says. It doesn't say "x has a current contract with every brand" or "x offers every brand for sale", it says "x has offered every brand at some point in history".

I think this is perhaps the biggest problem with 3NF and above: People construct sloppy queries (such as confusing "sells" with "has ever sold in the past"), verify that they give the expected results on a seven-row test table, and then deploy them in production. 3NF and above demand clear thinking.

I am an ardent fan of Postgres, and think it's important to properly store data for all the reasons mentioned elsewhere.

That said, there are cases where people get a bit too architecture astronaut with the DB relations, making simple things difficult before those complications are really necessary.

I'm not totally sure what you are getting at, but are you suggesting that the business rules described in the example (that salesmen are limited to selling certain products for certain brands) is not realistic in a real-world car dealership? That might be true, since it is just a simple example intended to demonstrate a concept.

If everybody can sell every product, obviously there is no reason to keep a list of who can sell what. You just keep a list of actual sales. But that is a question of designing the database to correspond to the business.

It has nothing to do with the merits of any of the normal forms. The normal forms does put any requirements on your business rules.

I once had an interview with an "architect" who was, in fact a trained architect (someone who designs buildings, he probably got into IT with the .COM bubble), who had zero training in DB theory.

We were chatting about database design, and he said the same line about normalization being about saving space in the old days.

Naturally, I declined the job offer. In fact, I made that one of my litmus tests for job hunting, along with "do your databases have referential integrity?", and "what's your opinion of primary keys?",

More likely got out of architecture with the S&L crisis (or more fully the Regan tax reforms that reduced the attraction of passive losses on real estate that triggered it). May have come into IT from CAD management which was somewhat on the cutting edge for small businesses in the early 1990's [e.g. Novell networks, megs of RAM, 80387's and multiple monitors...Acad with an nthengine and Herculese card was better than can't touch this baggie pants.]

I live in South Africa, so we didn't have an S&L crisis :) But yes, a lot of architects do play with CAD

I have to admit that the changes brought about by the S&L crisis in the US pale relative to the changes to South Africa's landscape in the early 1990's.

>> Please do not allow your data model designs to be artificially constrained by the requirements of the past.

I'm currently dealing with a large piece of software where many of the high profile bugs stem from data duplication errors. This isn't even using a DBMS it's happening in classes (which a very similar to tables BTW). I gave up DB programming as my primary job about 15 years ago, but the lessons of 3NF are applicable way beyond the DBMSs of the world. It's still quite relevant advise.

Great Frege's ghost! D:

Exactly. :-)

It's problematic to view the evolution of data modeling in the same way as the evolution of programming languages or paradigms associated with programming languages (including SQL).

So while languages and language-oriented paradigms that assist in abstractions (e.g. OOP) have changed a lot over the last few decades, the concept of data abstraction and modeling (relational data modeling in particular) has seen less dramatic evolutionary change.

I wonder whether this in itself makes it the reason why some folks believe there ought to be a change from an ostensibly outmoded and antiquated paradigm!

Great Frege's ghost indeed!!

Reply on news.ycombinator.com