6 ms·

Keep in mind that preserving space was of the utmost of importance in 1982. Eliminating data redundancy was considered the utmost of design, eschewing maintaina

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.

wvenable 12y ago
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 http://en.wikipedia.org/wiki/Don%27t_repeat_yourself
- zamalek 12y ago
  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/ http://antognini.ch/2010/01/join-elimination/ [2] http://en.wikipedia.org/wiki/Materialized_view http://en.wikipedia.org/wiki/Materialized_view
  - imanaccount247 12y ago
    >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.
    - zamalek 12y ago
      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.
    - wvenable 12y ago
      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?
      - zamalek 12y ago
        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.
        wvenable 12y ago
        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.
Joeri 12y ago
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.
- jzwinck 12y ago
  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!
  - Arnt 12y ago
    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.
    - davidw 12y ago
      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.
olavk 12y ago
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.
- collyw 12y ago
  >As far as I know, data integrity is still pretty important even today. Tell that to the NoSQL hipsters.
  - virmundi 12y ago
    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 https://leanpub.com/NewDataAFieldGuide. It's target at managers and other with little time to get a good idea.
    - phkahler 12y ago
      >> 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.
leoc 12y ago
Great Frege's ghost! D:
- ak39 12y ago
  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!!
ak39 12y ago
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.
buyx 12y ago
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?",
- brudgers 12y ago
  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.]
  - buyx 12y ago
    I live in South Africa, so we didn't have an S&L crisis :) But yes, a lot of architects do play with CAD
    - brudgers 12y ago
      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.
phkahler 12y ago
>> 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.

wvenable 12y ago

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 http://en.wikipedia.org/wiki/Don%27t_repeat_yourself

zamalek 12y ago
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/ http://antognini.ch/2010/01/join-elimination/ [2] http://en.wikipedia.org/wiki/Materialized_view http://en.wikipedia.org/wiki/Materialized_view
- imanaccount247 12y ago
  >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.
  - zamalek 12y ago
    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.
  - wvenable 12y ago
    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?
    - zamalek 12y ago
      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.
      - wvenable 12y ago
        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.

Joeri 12y ago

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.

jzwinck 12y ago
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!
- Arnt 12y ago
  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.
  - davidw 12y ago
    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.

olavk 12y ago

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.

collyw 12y ago
>As far as I know, data integrity is still pretty important even today. Tell that to the NoSQL hipsters.
- virmundi 12y ago
  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 https://leanpub.com/NewDataAFieldGuide. It's target at managers and other with little time to get a good idea.
  - phkahler 12y ago
    >> 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.

leoc 12y ago

Great Frege's ghost! D:

ak39 12y ago
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!!

ak39 12y ago

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.

buyx 12y ago

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?",

brudgers 12y ago
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.]
- buyx 12y ago
  I live in South Africa, so we didn't have an S&L crisis :) But yes, a lot of architects do play with CAD
  - brudgers 12y ago
    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.

phkahler 12y ago

>> 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.