1

In the following database, School_has_Student is a denormalized table :

enter image description here

Now if I decided to change the "paul" student name to "john" in the Student table, then the "paul" student name that exist twice in the School_has_Student table must be automatically changed to "john" to maintain data integrity.

Likewise, if the "paul" student name in one of the two rows of the School_has_Student table is changed to "john", then the other "paul" student name in the School_has_Student table and the "paul" student name in the Student table must be automatically changed to "john" to maintain data integrity.

But who make sure that these automatic changes happen, should I write a trigger to make sure these changes happen, or does the DBMS offer this feature out of the box (or does it depend on the DBMS used)?

Improve this question
4
  • If you ever thought the primary key might change, then you rethink the primary key. In other words, student name is an awful primary key. Names change all the time, and they are no more identifiers than someone's height is their identifier. Commented Oct 19, 2018 at 7:05
  • 2
    No one does. That's why denormalization is a bad idea unless you know you need it for performance reasons. Commented Oct 19, 2018 at 7:20
  • 1
    @Kilian Foth "No one does" But you said that sometimes denormalization is needed for performance reasons, so let's say that I need to use denormalization for performance reasons (assume that I tried every other option to increase performance but they weren't enough), am I suppose to just accept the fact that data integrity is lost without doing anything about it? Commented Oct 19, 2018 at 9:37
  • 2
    @Tom Whatever you do to ensure data integrity has a performance cost, so yes, because otherwise you are claiming that you can out perform the database engine. Commented Oct 19, 2018 at 12:11

3 Answers 3

Reset to default
6

But who make sure that these automatic changes happen, should I write a trigger to make sure these changes happen, or does the DBMS offer this feature out of the box (or does it depend on the DBMS used)?

It does offer this feature out-of-the box, it's called "normalization". If the value is only in one spot, changing it will mean all those that reference the spot have the latest value. As you intentionally avoided this, you have to do it manually, either with triggers or program logic.

At that point, you should ask yourself why you avoid the normalization. Normally it's done to gain something. Make sure that is the case, because re-implementing all you can have with a normalized database on top of a non-normalized database is not a gain. It's probably worse than what the database already could do out of the box. To gain something, you need to let go of something.

Improve this answer
1
  • "As you intentionally avoided this, you have to do it manually, either with triggers or program logic" I think you can also let the DBMS do it for you by using indexed views or materialised views. Commented Oct 21, 2018 at 19:46
2

What do you think you're gaining by having the student_name and school_name data duplicated across multiple tables. The over-riding tenet of database schema design should be "single source of truth", unless you have a very good reason.

Improve this answer
2

But who make sure that these automatic changes happen ...

Nobody. These changes are not automatic.

... does the DBMS offer this feature out of the box (or does it depend on the DBMS used)?

See [my reply to] your other post about using Views to [dynamically] create the denormalised form from a sensible, normalised one. As you suspect, different DBMSs offer different ways of doing this. Views will probably meet your needs for now. For really Big Data, you might need to resort to Materialized Views, where the database creates a table based on a View definition and then maintains that table in line with updates to your normalised tables. For really Big Data it's better, but you have [at least] two copies of the data so your database gets [much] bigger. With a View, you only store the "source" tables; the denormalised version is built "on the fly".

I think, by now, (after at least three posts on different fora) you've figured out that trying to maintain this denormalised "thing" for yourself is going to be absolutely horrendous. Keep "inside the box" and your life will be a whole lot easier.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.