Do you go beyond 3rd Normal Form? Would you?

[Banana]

I recently completed a database training workshop and had opportunity to talk with other database developers. One question I posted to quite few was "Do you ever had to go beyond 3rd Normal Form?" (to be pedantical, we're talking BCNF)

While I had expected most to say not often, I was taken aback when a couple said they *never* had ever needed to do so, saying that 3NF is more than good enough. Others never has done so but has known of databases written in since and asserted that if you can, more power to you.

I understand that the "3NF is just good enough" is based out of practicality; after all, if you're doing 8-way joins you'd see serious performance degradation even if your tables are properly normalized. I just didn't expect the "never" part. Furthermore, the developers said any benefits you would derive from 4NF or 5NF would be easily replicated in a custom set of functions written in front-end clients or with a given method of data validation. At least one has asserted that it would be easier to write hard codes than to normalize in fourth form.

So I'm broading my query to everyone else; have you ever had a database where going beyond 3NF benefited the database? Or would you agree that it would be easier to emulate the higher normalization using code/scripts/whatever?

 

[gemma-the-husky]

how can you tell?

are there tools that can trace normalization errors?

otherwise, you probably just normalise based on good sense/previous judgement/technique etc.

Occasionally knowingly introduce some deliberate redundancy.

I would think tracing every advanced normaliation issuesis probably an art rather than a science anyway.

Or am i missing something obvious

 

[The_Doc_Man]

At one extreme part of our US Navy Reserve database, there were times when we did as many as 15-way joins. Don't ASK me what that implies. But yes, we went beyond 3NF. All I know is that it applied to a reservist being assigned to a unit with some specific number of drills allocated and at the same time needing to know if the reservist had exceeded some allotment for the year or was drilling for points or was drilling for credit on an educational requirement (OJT) or some other stuff. Don't ask 'cause it was too long ago and I didn't write that application anyway.

No, before you ask: It wasn't done in Access. It was a Sharebase database running as a back-end to a rapid-application-development tool called SmartStar. When we told the techies at the ShareBase Corp. what we were doing, several of them became visibly pale. But hey, it worked great.

Don't go looking for Sharebase. Due to corporate shenanigans, they got (((bought by Teradata) bought by NCR) bought by AT&T - and divested back to NCR after the AT&T/NCR split. Then some of their disgruntled engineers went out and formed Sybase. After that, I lost track of the lot of them.

As always with any data set, you normalize based on the business model and the relationships of the real world. Those normalizations are called for when the data relations are complex enough that such normalizations make sense. Yes, 3NF is good and with Access, tends to cover a multitude of sins.

This leads to a question, though. WHY is 3NF good enough for Access? See, Access is not "special" in regard to normalization. It does the same general sorts of things that most of the other databases do.

But here's one possible answer. MOST (not all) of the time, you are working in smaller, less complex businesses that can't (yet) afford to go to one of the bigger solutions like SQL Server or ORACLE or some of the other back-end servers. And therefore, 3NF works very well for Access because very often the businesses that use it haven't had time to get really complicated yet. (Note the weasel-words: MOST, OFTEN, SOME, NOT YET ... no absolutes to be had in the bunch.)

 

[Banana]

Well, the class wasn't for Access; in fact, I was the only developer who used Access there. At least one developers I described in my OP was a veteran DBA working on Oracle.

If he had said, "I've never had the need but if you can, great!", I wouldn't have thought too much of it. But the assertion that 4NF/5NF is purely academic exercises, and that it can be done better using <insert your programming language> solution to handle the data anomaly instead of going upward in normalization quite surprised me. Hence me asking everyone else here about *would* they even bother going beyond 3NF?


...And, The_Doc_Man.... Freaking 15 joins!?!?! Shit, that beats britches out of my plithy database needing 4NF and 7-way joins to at least start to represent the real world model (note that I said start to, not actually represent). But if they can do it, and it worked satisfactory (can you tell if the performance was satisfactory? I'd hate to roll out an application that takes 10 seconds to drop down the list from combobox or something like that), then I guess why hell not?

I've already known that it's not specific to Access; normalization is true for any databases. It's the attitudes toward 4/5NF that surprised me.

 

[Banana]

Anybody else has an opinion?

 

[Rabbie]

As a rule of thumb 3NF/BCNF is usually enough for Access applications. It will depend on the complexity of your business model but taking an empirical view 3NF is often enough and you will finf then that there is nothing to be gained from further normalisation. Indeed you may well find that the DB is already in 4NF or even 5NF. There is no point in moving some fields to their own table if all you are doing is substituting a Key for the actual value.

 

[Banana]

Thanks for the response, Rabbie and Pat Hartman.

I want to add that my question wasn't only about whether you had done it before but also whether you *would*.

The premise is that whatever benefits one could gain from going beyond 3NF could be had for less time and work by using set of functions to perform data validation and that would be more faster and efficient overall than if you had to perform several joins to represent complex structures that's consequent of having them in 4NF or 5NF.

I, for one, am not 100% convinced that writing your own data valdiation functions would be better (whether in terms of efficiency, performance, man-hours) than properly designing the structure beyond 3NF. My reasoning is based on that a proper designed database will work on any front-end clients, on any platforms, whether doing it by code leads to the problem of making it accessible to whatever you are using and of course bugs, and plain stupid programmer's errors. Of course, I may be an ignorant goober who needs a good head-smacking and that's why I am asking "Would you?" among with "Have you ever?"

Looking forward to thoughtful replies.

 

[The_Doc_Man]

In the final analysis it is always "Does the problem require - and merit - the effort?" If you need and can justify 4NF then you SHOULD implement it. Same with 5NF or BCNF or just about any other NF. The key being the two-pronged question "need to do it and can justify the expense."

Yeah, we did a 15-way join. No, it wasn't that efficient, but then we never did that for our on-line menus. Only for batch-oriented reports that we used to run overnight.

Further, some of the joins were mere lookups. E.g. reservist's ZIP code --> State, county, city validation. Reservist's rank/rate designation --> pay grade. Reservist's "home" unit --> command center for reporting purposes. Stuff like that.

But maybe about 8 to 10 of those JOINs were indeed normalization issues OTHER than simple lookups.

Pat, you ain't gettin' senile - you're gettin' wise enough to know what to no longer care about.