1

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. – Ivo Coumans Oct 18 '18 at 15:01
  • So proper indexes and views to denormalize the data aren't working? – Greg Burghardt Oct 18 '18 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 '18 at 6:57
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.

2

... 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 ; 
0

Let's say that you have identified strong arguments for a denormalised table (e.g. convenience for queries with third party tools, performance, distributed databases, etc...).

If you have all the columns of your original tables in your redundant table, you could consider to remove the original tables.

However, I'd strongly advise you to keep the original tables for the following reasons:

  • your School and Student tables may have additional non-redundant data (e.g. address of the school, last degree of the student).
  • your School and Student tables could evolve and sooner or later need such additional data, and that it might not necessarily relevant for the already developed components.
  • data access policy for additional School or Student data might be different than for School_has_student. For example privacy constraints (GDPR ?) could require that age and address of Student is restricted to some administrative staff with special priviledges.
  • changes to School and Student should be channelled via the original tables, especially if you want to implement some transaction logic and locking.
  • managing entity identity with unique IDs for School and Student is easier with the original tables, since you could use auto-numbering feature in most RDBMSes, whereas this feature would not be usable with the denormlized table. In addition, the use of referential integrity, can avoid typing errors and inconsistencies.
  • evolution of the overall DB scheme is to be expected, for example with an additional many-to-many relation like School - Teacher. The consistency of the data would be easy to implement with the original tables (again, with some denormalisation in the new relation if needed). But how would you ensure such consistency without the original "parent tables" ?
0

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.