Search code examples
sql-serverdatabase-designnormalizationredundancydatabase-normalization

Normalising DataBase with MultiValued Dependency (4NF/5NF)

I am planning to do a job portal database. Here I have a some candidates subscribing to some notifications.

Table preview

(of course candidate name will be ID & the candidate have more columns associated with the person, which are not shown.) I'm confused whether it is a 4th or 5th normal form problem! and I need some help to reduce redundancy.

Can I split this to something like these tables without loosing IsEnabled info so that I could remove the redundancy?

Candidate Table

and

Notification Table

Is it possible or is this a case of unavoidable redundancy?

Solution

  • It's not a normal form problem from any angle you look at it.

    If you want to keep that first table as it is (including the boolean attribute), then what you have is a CONSTRAINT. To the effect that in that first table, there must a row present for every possible combination of (candidate, notification type). But that is not a NF problem ! It would only be a NF problem if it were somehow possible to determine what the boolean value should be, given only candidate or given only notification type.

    What you wrongly perceive as being "redundancy" in this approach, is precisely the presence of that constraint, and its effect of making life difficult on you when you are updating the database.

    Ditch the boolean attribute and write only those rows in that first table that have "true". Now you no longer have that constraint, and all there is is just a regular "junction" table linking candidates to enabled notification types, just like there are zillions of "junction" tables in databases all over the world.