Say I have the following ER diagram:

enter image description here

To implement the above ER diagram as a normalized database, I would create a School table, and a Student table, and a School_has_Student table (that only contains the school_id column and the student_id column):

enter image description here

But let's say that I want to denormalize my database, this means that the School_has_Student table must now contain all of the School table columns and all of the Student table columns:

enter image description here

Now the School_has_Student table contain all of the students and schools data.

So my question is, when denormalizing a database, do I also need to keep the parent tables in the database or can I delete them? so in my example, do I need to keep the School table and the Student table in the database or can I delete them?

  • 1
    As far as I know, the 'trick' to denormalization is to find ways to keep the parent table and the denormalized table in sync whilst improving performance. So you should keep the parent tables. I'm no expert on the subject, though, so I'll leave this as a comment rather than an answer. Oct 18, 2018 at 15:01
  • So proper indexes and views to denormalize the data aren't working? Oct 18, 2018 at 16:48
  • @Greg Burghardt I did not ask this question to solve a problem I am having, but rather I am trying to learn about denormalization.
    – Tom
    Oct 19, 2018 at 6:57

3 Answers 3


Your second example is indeed denormalized, and the original tables are now redundant – but this is not the only way to denormalize data, and I would venture to say that cases like this are rare. Denormalization is a performance optimization, which means that you do it because you have performance issues. These issues are different from case to case, so the ways to solve them also differ.

Keep in mind that denormalization has a cost. Updating the name of a school is trivial in your first example, but costly in the second one.

I don't think there's much to be gained by putting the school_name column in School_has_Student - how often do you look up the name of a school, given a student, in bulk? What kind of volume will that type of query reasonably reach? The opposite (looking up the names of all students at a school) is more likely to be taxing, so keeping student_name might make sense. Now Student is redundant, but School is not.

Another example of denormalizing the same original database is to add a number_of_students column to the School table. This optimizes for a specific type of questions ("How many students does this school have?", "Which school has the most students?", etc.) by removing the need to look at multiple rows in the School_has_Student table, but all tables are still necessary for other queries.

If students also had genders, you would have to keep the Student table since those are not included in the School_has_Student table. You might also keep the gender ratio in the School table (another denormalization), and now you have no redundant tables.

You can remove redundant tables, but you're not likely to have them.


... do I need to keep the School table and the Student table in the database or can I delete them?

As ever, it depends.

As a source for [particular] reports, your denormalised form is reasonable.

For any kind of transaction processing (update) work, it is not.

If anything, I would advocate having both. This is exactly what a View (or, possibly, a Materialized View, depending on your DBMS) can get you. Both generate the desired "shape" of your denormalised table and both guarantee that this denormalised form will be in step with the the sensibly-updatable, normalised tables.

create view SchoolStudentView as 
select ss.school_id 
,      sch.school_name 
,      ss.student_id 
,      stu.student_name 
from       school_students  ss 
inner join schools  sch 
      on   ss.school_id = sch.id 
inner join students  stu 
      on   ss.student_id = stu.id ; 

Quite often a denormalised, transformed copy of a database is used (for reporting, analysis etc.) rather than mucking up your actual OLTP database. It's not updated at the time the "proper" version is, just extracted and rebuilt periodically. In these cases you don't really have to keep the original as your data is pre-cooked to your requirements. The original tables are available in staging and only used during the ETL. It really depends on your use case though. I'd steer away until you need it and even then don't try and solve every problem in the same way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.