March 21, 2012 at 4:03 pm
Technically you can normalize the crap out of any entity model. this means adding more tables, primary/foreign key links etc.
And although you will have a very well normalized database, your sprocs may be much more complicated. so where do you draw the line?
Let me ask with a scenario:
-A college has students.
-Students take a course.
-Each course has a number of subjects
-Each student takes a subset of subjects
-A student takes a course at a certain level (Advanced, intermediate, basic)and they gain a certain grade (A, B, C, D, E)
So we have a students table.
-We have a course table
-we have a subjects table
-we have a students_Courses table linking students with the course they are taking
-we have a SubjectLevel table containing master data for the subjectLevel
-We have a Grade table containing all available grades.
-we have a Student_subject_level_Grade table linking students with the subject, level and grade they got.
Is having lots of bridging tables that link entities over-normalization (such as the Student_subject_level_grade table)?
March 21, 2012 at 4:57 pm
The more normalized the schema, the more overhead there will be in constituting a resultset that crosses many normalization boundaries, i.e. more joins will be necessary for your read operations. That said, the faster your random inserts updates and deletes can be since you'll have less redundant data and presumably less empty columns. Generally speaking, the more normalized you are, the more create, update and delete transactions you can handle. This may not be the answer you're looking for, but barring taking your normalization strategy to the absurd it will depend on the workload as to how much normalization will be too much.
This is a good example of a challenge we all look in the face once in a while about how to choose the level of normalization for our data model. Here is a good discussion of just such a challenge by MVP Aaron Bertrand: Storing E-mail addresses more efficiently in SQL Server[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2012 at 6:58 am
winston Smith (3/21/2012)
Technically you can normalize the crap out of any entity model. this means adding more tables, primary/foreign key links etc.And although you will have a very well normalized database, your sprocs may be much more complicated. so where do you draw the line?
Assuming post refers to an OLTP system the answer is simple, usually the Third Normal Form a.k.a. 3NF does the trick.
Remember the old saying; "normalize until it hurts, denormalize until it performs" - which in my experience for OLTP happens about 3NF.
By the way, love your techincal expression about "normalize the crap out of..." 😀
_____________________________________
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.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply