Hopefully this will show up on the first page of Bing/Google when someone types in something to the tune of 'AX 2012 database normalization'. <- 'Gratuitous line for search engines'
What is database normalization?
Database normalization (n) definition - (WIKIPEDIA) - Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships. http://en.wikipedia.org/wiki/Database_normalization
Sounds awesome right? Can be, not always. There are pros and cons to it. Sometimes a denormalization structure is best. I don't think anyone will agree that normalization is the answer 100%. While there are more points to the below, I'll only cover a few basic ones.
- Updating tables can be faster (fewer fields)
- Can be less risk of database locking
- More flexibility to expand functionality of solution
- Removes or reduces redundancy
- Increase data retrieval times for single tasks (if designed correctly)
- Enforce relationships between data in joined tables (referential integrity)
- Can split up a table with many fields that will be quite large into multiple database servers
- Indexing strategies not as effective as data is spread out across multiple tables
- If a developer doesn't know what they are doing (or requirements are incorrectly identified), it can create a mess very quickly
- don't have to worry about joins in a denormalized structure.
- reporting and integration can be an easier task than white boarding out a network of tables.
- Indexing can be more effective
- Easier to maintain and understand schema
- Good approach to consider when CPU is an issue
- Updating tables might be slower
- Updating development on one large table with many fields
- Retrieving records may be slower
The ultimate answer is to know both normalized and denormalized DB strategies. Through thorough planning of a solution, you can determine the best approach. As for asking about database normalization, I think the best initial questions to ask up front are:
a) are the developers of the solution competent
b) did they take normalization into account when designing the solution
For more information, I strongly encourage you to do some more research into these concepts. It can only help!
Very awesome post! I like that and very interesting content.ReplyDelete
Microsoft Dynamics AX Training
MS Dynamics AX Training
MS Dynamics Training in Hyderabad
Microsoft Dynamics AX Technical Training
Microsoft Dynamics AX Technical Training in Hyderabad
oracle scm trainingReplyDelete
app v training
windows admin training
sap fico training
sap fico training
Informative article, just what I was looking fo. You can also check about database schema convert into normal form from elstel.orgReplyDelete
Thanks for this blog keep sharing your thoughts like this...ReplyDelete
Oracle Training in Chennai
Oracle Online Training
Oracle Training in Bangalore
Oracle Training in Coimbatore