September 9, 2009 at 8:29 am
It is always a laudible goal to get to at least 3NF, but let's be real, how many DBA's here scratch their heads more than once when the developers give them yet another set of scripts to create glorified spreadsheets? In theory, you can get as normalized as you want to keep the queries efficient and manageable, meeting business requirements, but sometimes, the business needs some easy to read way of accessing the data.
In SQL Server, would views be the ideal way to de-normalize the tables and how much would it impact performance? I guess the reality is that this would never happen in any and all cases. Anyone here made headway into this issue of normalizing all/most of their databases?
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
September 9, 2009 at 8:41 am
The key is to keep core structure in 3NF and be prepared to allow some degree of de-normalization in reporting delivery tables.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 9, 2009 at 10:18 am
For and non-trivial system, I'd go for
OLTP - Normalisation
Reporting - from datawarehouse using dimensional modelling
September 9, 2009 at 12:22 pm
Andrew Gothard (9/9/2009)
For and non-trivial system, I'd go forOLTP - Normalisation
Reporting - from datawarehouse using dimensional modelling
True, in the best of all possible worlds, all reporting would be from a separate datawarehouse. Thanks for the food for thought.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
September 18, 2009 at 3:12 am
PaulB-TheOneAndOnly (9/9/2009)
The key is to keep core structure in 3NF and be prepared to allow some degree of de-normalization in reporting delivery tables.
I agree.
"Keep Trying"
September 24, 2009 at 7:55 am
If I am allowed to venture a comment on this, I would say, The DBA should know when to enforce Normalization and when to DE-normalize. 🙂 There are times when the developer comes with table Scripts that are normalized and we have to advice them that it is better to de-normalize these tables for better performance.
Or does all the DBAs here against me on this? 🙂
-Roy
September 24, 2009 at 2:30 pm
Roy Ernest (9/24/2009)
If I am allowed to venture a comment on this, I would say, The DBA should know when to enforce Normalization and when to DE-normalize. 🙂 There are times when the developer comes with table Scripts that are normalized and we have to advice them that it is better to de-normalize these tables for better performance.
How is that different than that brilliant post over there from... mmhh... hey!... from myself!!! that reads "The key is to keep core structure in 3NF and be prepared to allow some degree of de-normalization in reporting delivery tables"?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 24, 2009 at 2:39 pm
Simple, I did not understand your Techinical jargon. :hehe: Sorry, did not read all posts. My apologies.
-Roy
September 25, 2009 at 8:10 am
For our data acquisition systems, all are at 3rd level normalization.
Our data warehouse uses the Kimball Star Schema design.
But. here is a real life example. Our server management team asked if we could put their spreadsheet on SQL-Server.
When I analyzed the data in their spreadsheet, I found many different spellings for the same condition, different abbreviations, different ways of referring to version or release. So, I created a relational database normalized to 3rd form. In the process, I analyzed all of their text fields and broke them into component parts such as manufacturer, model, disk size, storage type, etc.
Then, I went back to the group to show them what I had.
It turned out that they were perfectlly happy with their spreadsheet. The only problem was that it could be opened for update by only one person at a time. All they wanted was the data in a database so that more than one person could have it open for update at a time.
My analysis and normalized structure was nothing they could use because they did not have the means to create the application to use with it.
So, in this case, normalization was not the solution.
David
October 2, 2009 at 7:19 am
David. (9/25/2009)
For our data acquisition systems, all are at 3rd level normalization.Our data warehouse uses the Kimball Star Schema design.
I'm in full agreement with David's post above.
In regards to the shareable spreadsheet experience; I've seen that before. Any attempt to build a SQL Server based "application" out of an Excel spreadsheet will face furious and tenacious resistance. Leason learned - in my case, don't overkill.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 10, 2009 at 1:25 pm
GabyYYZ (9/9/2009)
Anyone here made headway into this issue of normalizing all/most of their databases?
The widespread adoption of Agile development principles seems to be having some positive influence on database design. A normal form model is good for iterative development because it minimises any "bias" in the design and therefore makes it easier to refactor.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply