Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 9 down vote favorite
5

I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)

share|improve this question
3  
I think the main problem behind this is that the people writing the ORMs are not database experts, they're programmers who are trying to get a job done and they don't always want to have to deal with database best practises as they have a lot of other important stuff to do. they just want some automatic tool to hold all their data do they can get on with their job. – Jharwood Jun 26 '12 at 8:23
@Jharwood please don't post comments as answers. I realize that you don't have enough rep to make comments yet but that doesn't make posting non-answers as answers acceptable. – JNK♦ Jun 26 '12 at 12:12
@Jharwood, masking the database design correct and normalized is important stuff. A badly designed database will kill your application eventually. – HLGEM Jun 26 '12 at 18:32
@Jharwood which is why development should include a team with varying expertises. DBAs aren't cranky and mean, just seeking to keep a coder from doing their job. They have a specific skillset and so does the coder. – swasheck Jun 27 '12 at 17:52
1  
@Nupul, It seems very unlikely that the specified example of a surrogate key and a key consisting of an email address would be in violation of 2NF or BCNF. 2NF is violated only where there is a partial-key dependency. – sqlvogel Jun 29 '12 at 16:17
show 1 more comment

5 Answers

up vote 12 down vote accepted

One reason for normalisation is to remove data modification anomalies
ORMs usually do not support this.

I have many examples of Hibernate-designed databases that break this principle:

  • bloated (string repeated over 100s of millions of rows)
  • no lookup tables (see above)
  • no DRI (constraints, keys)
  • varchar clustered indexes
  • unnecessary link tables (eg enforcing 1..0:1 when a nullable FK column would suffice)

The worst I've seen is a 1TB MySQL database that was perhaps 75-80% too big because of these

I'd also suggest that the statement "todays systems can handle it" is true for most Mickey Mouse systems. As you scale, today's systems won't.

In my example above, there was no traction to refactor or change keys or fix data: just complain about database growth rates and inability to build a meaningful DW on top of it

share|improve this answer
3  
"ORMs, keeping database folk in business since Hibernate was born" – Mark Storey-Smith Jun 26 '12 at 9:45
up vote 5 down vote

I agree that memory constraints did bear a direct correlation to normalization...

Memory constraints still matter. Quantity isn't a problem, speed is.

  • CPUs aren't getting any faster at the moment (We're getting more core's, not cycles per second)
  • Modern CPU architectures attempt to overcome the speed limitation by providing separate memory for each processor (NUMA).
  • On-die cache sizes aren't growing at a comparable rate to main memory.
  • Memory throughput is not as high as most people expect. QPI is in the region of 25GB/sec.

Some of this ground was covered in When to use TINYINT over INT? which you might find useful. I'd also suggest following the antics of @ThomasKejser (blog) from the SQLCAT team, as they tend to be at the sharp edge of pushing database performance. The recent post on The Effect of CPU Caches and Memory Access Patterns and the SQLBits presentation on Relational Modeling for Extreme DW Scale are good examples.

share|improve this answer
up vote 5 down vote

The more things change, the more they stay the same. There have always been lazy developers who cut corners or just don't know or want to follow best practices. A lot of the time they can get away with it in smaller applications.

It used to be jamming COBOL-inspired data structures into early RDBMS, or the God-awful mess that was dBase. Now it's ORMs and "Code-First". In the end, these are all just ways of people trying to find the silver bullet of getting a working system without "wasting" time thinking hard about what you want and need to do. Being in a hurry has always be a problem and will always be a problem.

For those who have the good sense (and good luck) to take the time to design properly, the data model will always be the most logical place to start. What goes in the database is information about the things (tangible and intangible) about which your business cares. What your business cares about changes much less quickly than how your business operates. This is why your database is generally much more stable than your code.

The database is the rightful foundation of any system and taking the time to lay your foundations properly will inevitably benefit you over the long run. That means that normalization will always be an important, useful step for any OLTP type application.

share|improve this answer
I don't disagree with your overall sentiment, but I think you need to be careful about laying the blame at 'avoiding wasted time'. According to my understanding of ORMs (note: not an expert) one key principle is to avoid unnecessary duplication - e.g. defining the same data structures in code and the database - which sounds an awful lot like the database redundancy many of the posts here - rightfully, IMO - want to avoid. In other words, if done right, shouldn't an ORM approach be in everyone's interest? – Bobby Jack Jun 28 '12 at 12:17
2  
I'm not saying ORMs are bad. I use them myself. I've even built my own back in the good old days before there were ORM frameworks. My point is that people who look at any technology, ORM or otherwise, as being a magical answer that will let them deliver working systems "without code" are fooling themselves. Technologies like ORM can eliminate a lot of grunt work for programmers, but they can't eliminate careful analysis and design. – Joel Brown Jun 28 '12 at 18:39
@BoddyJack as is often discussed, all non-trivial abstractions, to some degree, are leaky. ORMs are most definitely non-trivial. It's a trade-off. You can boost early stage development productivity if you accept the risk that you might be plugging leaks later. – Mark Storey-Smith Jun 28 '12 at 23:00
up vote 1 down vote

In my opinion, it is still just about balance between normalize & de-normalize. I totally agree that ORM frameworks are merely approaches getting things done, but I don't think it is these frameworks causing the de-normalize trend.

it is still that debate you want time efficiency or you want space efficiency. At the time Relational Database theory is brought up, the disk storage is expensive, people obviously don't want to spend that much money on this, that is why at that time relational databases are the one who stands firm amid adversities

Now days things are quite different, storage are very very cheap. So obviously we can tolerate more redundancy compared with old days, this is also WHY the BIG_TABLE approach appeared. in order to seeking more time efficiency , the space efficiency has to be sacrificed.

But, Big-table approach is not the end of story either, it is still the balance between time and space, in terms of PB volume data to manage, some developer also began to seek the balance back to space efficiency, that is why there are works done to normalize some data in BIG-TABLE like structures.

In a word, the normalization approach is not dead definitely, but compared with the old days it is definitely overlooked.

share|improve this answer
Frameworks may do not cause the trend but they do help in that direction. – ypercube Jun 28 '12 at 9:23
2  
I disagree. Normalization does not exist to reduce database size due to redundancy. That is merely a side benefit. The true purpose behind eliminating redundancy is data integrity. As redundancy increases, data modification anomalies increase. This is a proven mathematical concept over the last 40 years. Regardless of (and completely independent of) the price of storage, data integrity is king (or should be), thus your transactional database should be normalized. Period. "Tolerating redundancy" means tolerating risk to data integrity. Not something I'm willing to do with my data – sqlbattsman Jun 29 '12 at 13:14
The other thing to keep in mind: The #1 reason I have heard throughout my career is people arguing to denormalize to improve performance. "The more normalized a database is the worst it performs." That simply is no longer true. RDBMS vendors recognized long ago that normalization is a REQUIREMENT to maintain data integrity, but their products did not handle normalized databases as well as they should. So for the last 25 years they have been perfecting their products, and any RDBMS released in the last 10 years is carefully optimized for NORMALIZED databases, NOT denormalized – sqlbattsman Jun 29 '12 at 13:17
@sqlbattsman I agree data integrity is the king; but DE-Normalizing for performance, it is almost the case, a lot of companies are trying to turn to BIGTABLE. when you say RDBMS is not performing as well as it should be, which aspect are to be improved ? and are they related with de-normalizing ? – zinking Jul 1 '12 at 3:47
up vote 4 down vote

it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright.

Surrogate keys don't break 2NF. 2NF says "If a column is dependant on only part of a multi-valued key, remove that column to a separate table."

They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls

Having several columns in one table is valid as long as Normalization rules are followed. It is not correct to merge tables without analysis if you want to reap the benefits of SQL and normalization.

I agree that memory constraints did bear a direct correlation to normalization Relation Normal Forms is a mathematical concept and has nothing to do with memory.

Normalization is there not only to save memory or disk, it is there to add integrity. After all it is a mathematical concept independent of hardware.

Simple Example: Say you maintain school information as:

Rec 1:North Ridge High School, California, USA

Rec 2:South Toronto Braves High School, Toronto, Canada

If you ask your system where is Toronto, you can find out that it is in Canada. Few days later you delete the 2nd row and ask the system the same question and you get nothing. In this example, no mater how much disk space or memory or CPU, you will not get the answer.

This is one anomaly normalizing relations help prevent.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.