0

I have this simplified table:

enter image description here

The business states that anyone with a role_id of 3 needs to have a leader_id, while anyone with any other value for role_id should not have a leader_id (null).

By knowing the value of leader_id we now narrow down the possible values for role_id. Have I unwillingly created a transitive dependency between leader_id and role_id?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Lasse
  • 77
  • 1
  • 10
  • Does a `leader_id` value _uniquely_ determine the value for `role_id` ? – Tim Biegeleisen Jun 05 '17 at 06:15
  • Any value other than `null` in the `leader_id` column would require the `role_id` column to be `3`. – Lasse Jun 05 '17 at 06:17
  • I see a problem with mapping `leader_id` to `role_id`. We can't have `NULL` mapping to both `1` and `2`. Maybe you should be mapping `role_id` to `leader_id` instead? – Tim Biegeleisen Jun 05 '17 at 06:18
  • This feels more like a constraint than anything to do with dependencies – Phil Jun 05 '17 at 06:22
  • This is a business constraint, not an actual DB dependency, yes. – Lasse Jun 05 '17 at 06:41
  • What *reference* for normalization are you using? What are *definitions* of "FD" & "partial FD"? *When* (ie in what *other definition*) are partial FDs problematic? Also, your question cannot be answered until you determine all the FDs (functional dependencies) of the table. "By knowing the value" is just too sloppy to be of use. Also: Normalization theory does not deal with NULL. If no nullable column is part of a non-trivial FD's determinant then you can normalize as usual. Otherwise you must convert to such a design, normalize, then denormalize. – philipxy Jun 11 '17 at 02:57
  • There is no difference between "a business constraint" and "an actual DB dependency". (Tables state how the business situation is, so saying that the table values are limited is the same as saying that the business situations are limited.) – philipxy Jun 12 '17 at 10:05

1 Answers1

1

No it is not a transitive-dependency : the role alone doesn't provide you with enough information to find the leader; also, the leader alone doesn't provide enough information to find the role (for null values).

A transitive-dependency means that you must be able to discover a value X from another value Y with a perfect score.

For exemple, if you know the City, you obviously know the Country as well, you don't have to create two columns (city and country), you only need the city, and by using the transitive-dependency, you will get the country.

Guillaume F.
  • 5,905
  • 2
  • 31
  • 59