|
|
Posted by Jerry Stuckle on 07/01/05 05:05
Jeff North wrote:
>
>
> Normalisation is good.
>
> But there are databases that are more efficient when not normalised.
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ssceqpop.mspx
> and scroll to "Consider Database De-normalization".
>
> IOW there are times when you should normalise and there are times when
> you shouldn't normalise.
>
Jeff,
Yes, I am QUITE familiar with database normalization and denormalization. I've
been doing relational databases for over 20 years, starting with DB2 on
mainframes. (I was working for IBM at the time). I've designed hundreds of
databases, from small one or two tables ones to databases with dozens of tables
and multiple foreign keys in each table. The largest I remember doing had
upwards of 80 tables, over 600 columns and, when populated, exceeded five
terabytes in size.
About the only *valid* reason for denormalizing is performance. The more you
normalize, the more you need to join tables, causing potential performance
problems. This is why most relational database never go beyond 3rd normal form.
In larger database, some tables might only be at 2nd normal form, creating a
hybrid "2.5 normal" database.
But proper normalization also limits changes to the database structure. No, in
your case you do not have any duplicate data at this time. However, you do have
poor design because adding a new language requires you to change the database
structure. This should also be avoided, because changes to the database
structure potentially affect every program using the database - and all code
using the database must be examined. This can be a long process when there is a
lot of code. My solution prevents this.
And yes, you could have duplicate data. For instance - you might have a message
"I'm sorry, this item is not available in your country" in French and Spanish,
but not English or Italian. No, my design won't cure that either - which is why
it's not a 3rd normal design. But this I didn't think it would be necessary in
this instance - the amount of duplicate data, if any, is minimal.
> Now lets throw the cat amongst the pidgeons :-)
>
>
> True. But (there's always a but isn't there :-) ) what if there are
> multiple areas on the page? What if the page is a form with multiple
> fields?
>
> A (slightly) different table structure will need to be used.
> I've shown one method, you've shown another.
> Which is better - depends upon your requirements.
>
> Lets consider another common layout.
> You have a page with 3 columns.
> Column 1 is the menu.
> Column 2 is the content.
> Column 3 is adverts specific to that country.
>
> In the above case neither of our table designs would be suitable.
>
I also gave a slightly more complex design (3 of them, actually) which do solve
that problem. And they are normalized.
>
>
> I think you are making the assumption that I'm returning all fields
> from the table, I'm not.
>
No, but the database still fetches the entire row before selecting which ones to
return.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|